Please help me, how to filter words in SQL using a function?
I'm having a hard time if I explain it so I'm giving example:
ID | WebsiteName |
-----------------------------------
1 | www.yahoo.com |
2 | www.google.com |
3 | www.youtube.com |
What I want is, how to get the name of the website. I want to select the record with an output like this. How to remove the 'www.' and '.com' in the record.
ID | WebsiteName
--------------------------
1 | yahoo
thanks for the help. :D
This question is related to
sql-server-2008
I can give a small hack, you can use T-SQL function. Try this:
SELECT ID, PARSENAME(WebsiteName, 2)
FROM dbo.YourTable .....
This will work for most of the website names :
SELECT ID, REVERSE(PARSENAME(REVERSE(WebsiteName), 2)) FROM dbo.YourTable .....
You can use stuff in place of replace for avoiding the bug that Hamlet Hakobyan has mentioned
CREATE FUNCTION dbo.StripWWWandCom (@input VARCHAR(250))
RETURNS VARCHAR(250)
AS BEGIN
DECLARE @Work VARCHAR(250)
SET @Work = @Input
--SET @Work = REPLACE(@Work, 'www.', '')
SET @Work = Stuff(@Work,1,4, '')
SET @Work = REPLACE(@Work, '.com', '')
RETURN @work
END
This one get everything between the "." characters. Please note this won't work for more complex URLs like "www.somesite.co.uk" Ideally the function would check for how many instances of the "." character and choose the substring accordingly.
CREATE FUNCTION dbo.GetURL (@URL VARCHAR(250))
RETURNS VARCHAR(250)
AS BEGIN
DECLARE @Work VARCHAR(250)
SET @Work = @URL
SET @Work = SUBSTRING(@work, CHARINDEX('.', @work) + 1, LEN(@work))
SET @Work = SUBSTRING(@work, 0, CHARINDEX('.', @work))
--Alternate:
--SET @Work = SUBSTRING(@work, CHARINDEX('.', @work) + 1, CHARINDEX('.', @work) + 1)
RETURN @work
END
Source: Stackoverflow.com