Thanks all for your responses and tips! Although there were different ways to tackle, I needed the fastest way to accomplish it because of time constraint and it was for one time run only. I picked up this UDF from some website and tailored it to
my need, Now I cannot seem to find that address so that I can cite it here . But whoever the person who wrote it, saved me lot of time and got my small project done. So God Bless him!!!!
--I'm just pasting it here so somebody might use it later----
CREATE FUNCTION [dbo].[usp_Parse_domain_name_v5] (@strURL varchar(1000))
RETURNS varchar(1000)
AS
BEGIN
IF CHARINDEX('http://',@strURL) > 0 OR CHARINDEX('https://',@strURL) > 0
SELECT @strURL = REPLACE(@strURL,'https://','')
SELECT @strURL = REPLACE(@strURL,'http://','')
SELECT @strURL = REPLACE(@strURL,'www','')
SELECT @strURL = REPLACE(@strURL,'www.','')
SELECT @strURL = REPLACE(@strURL,'ftp://','')
-- Remove everything after "/" if one exists
IF CHARINDEX('/',@strURL) > 0 (SELECT @strURL = LEFT(@strURL,CHARINDEX('/',@strURL)-1))
--Optional: Remove subdomains but differentiate between www.google.com and www.google.com.au
--IF (LEN(@strURL)-LEN(REPLACE(@strURL,'.','')))/LEN('.') < 3 -- if there are less than 3 periods
--SELECT @strURL = PARSENAME(@strURL,2) + '.' + PARSENAME(@strURL,1)
--ELSE -- It's likely a google.co.uk, or google.com.au
--SELECT @strURL = PARSENAME(@strURL,3) + '.' + PARSENAME(@strURL,2) + '.' + PARSENAME(@strURL,1)
RETURN @strURL
END
GO
-
Marked as answer by
rok1
Tuesday, March 22, 2011 10:47 PM