CODEWITHSUNDEEP
sqlsql-serversql-server-2008
megastrong001
megastrong001

Reputation: 897

How can I filter words in SQL using a function?

How can I filter words in SQL using a function?

I'm having a hard time if I explain it, so I'm giving an example:

ID WebsiteName
1 www.yahoo.com
2 www.google.com
3 www.youtube.com

What I want is to get the name of the website. I want to select the record with an output like this. How can I remove the 'www.' and '.com' in the record?

ID WebsiteName
1 yahoo

Upvotes: 86

Views: 416915

Answers (5)

Balaji
Balaji

Reputation: 11

This will work for most of the website names :

SELECT ID, REVERSE(PARSENAME(REVERSE(WebsiteName), 2)) FROM dbo.YourTable .....

Upvotes: 1

marc_s
marc_s

Reputation: 755321

How about this?

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 = REPLACE(@Work, '.com', '')

    RETURN @work
END

and then use:

SELECT ID, dbo.StripWWWandCom (WebsiteName)
FROM dbo.YourTable .....

Of course, this is severely limited in that it will only strip www. at the beginning and .com at the end - nothing else (so it won't work on other host machine names like smtp.yahoo.com and other internet domains such as .org, .edu, .de and etc.)

Upvotes: 145

Akki
Akki

Reputation: 51

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

Upvotes: 5

Nicole Castle
Nicole Castle

Reputation: 410

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

Upvotes: 13

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

I can give a small hack, you can use T-SQL function. Try this:

SELECT ID, PARSENAME(WebsiteName, 2)
FROM dbo.YourTable .....

Upvotes: 9

Related Questions