CODE: clean urls

SELECT     TOP (100) PERCENT { fn LCASE((CASE WHEN CHARINDEX(‘/’, url) = 0 THEN ‘http://www.’ + REPLACE(REPLACE(url, ‘www3.’, ”), ‘www.’, ”)
ELSE CASE WHEN (url) LIKE ‘www’ THEN ” ELSE ‘http://www.’ + REPLACE(REPLACE(REPLACE(LEFT(url, CHARINDEX(‘/’, url)), ‘www3.’, ”), ‘www.’, ”), ‘/’,
”) END END)) } AS websiteurl, id
FROM         dbo.tbl_contacts
WHERE     (id IN
(SELECT     TOP (100) PERCENT MAX(id) AS keyid
FROM          (SELECT DISTINCT
TOP (100) PERCENT { fn LCASE((CASE WHEN CHARINDEX(‘/’, url) = 0 THEN ‘http://www.’ + REPLACE(REPLACE(url, ‘www3.’, ”),
‘www.’, ”) ELSE CASE WHEN (url) LIKE ‘www’ THEN ” ELSE ‘http://www.’ + REPLACE(REPLACE(REPLACE(LEFT(url,
CHARINDEX(‘/’, url)), ‘www3.’, ”), ‘www.’, ”), ‘/’, ”) END END)) } AS websiteurl, id
FROM          dbo.tbl_contacts AS tbl_contacts_1
WHERE      (url LIKE ‘%.me.uk%’) OR
(url LIKE ‘%.co.uk%’) OR
(url LIKE ‘%.com%’) OR
(url LIKE ‘%.net%’) OR
(url LIKE ‘%.info%’) OR
(url LIKE ‘%.org.uk%’) OR
(url LIKE ‘%.biz%’) OR
(url LIKE ‘%.org%’) OR
(url LIKE ‘%.tv%’)) AS O
GROUP BY websiteurl
ORDER BY websiteurl))

Software engineers do the job better!