Clean a column of urls with mixed HTTP/HTTPS and www. prefixes

url_split.sql

WITH sample_data AS (
  SELECT 'https://www.example.com' AS url
)
SELECT 
  CASE 
    WHEN split_part(split_part(url, '://', 1), 'www.', 1) = url THEN url
    WHEN split_part(url, '://', 1) = url and split_part(url, 'www.', 1) != url THEN split_part(url, 'www.', 2)
    WHEN split_part(url, '://', 1) != url and split_part(url, 'www.', 1) = url THEN split_part(url, '://', 2)
    ELSE split_part(split_part(url, '://', 2), 'www.', 2)
  END as result
FROM sample_data