How to create a function in SQL Server

Question

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

You haven't explained adequately. Do you want to do this just during a `SELECT`, or as a computed column? Do you wish to just to extract a domain name at one level (how would you like `www.example.co.uk` to be treated?)? — Damien_The_Unbeliever, Jan 27 '13 at 18:16

score 139 · Accepted Answer · edited Jul 10 '17 at 16:17

139

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.)

edited Jul 10 '17 at 16:17

Star Light

154
2
8
19

answered Jan 27 '13 at 18:16

marc_s

732,580
175
1,330
1,459

2

You could use the `STUFF` and `CHARINDEX` like this: `SET @Work = STUFF(@Work, CHARINDEX('www.', @Work), LEN('www.'), '')` – Winnifred Apr 26 '18 at 19:41

score 13 · Answer 2 · answered Jan 28 '13 at 16:19

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

score 9 · Answer 3 · answered Jan 27 '13 at 18:28

9

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

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

answered Jan 27 '13 at 18:28

Hamlet Hakobyan

32,965
6
52
68

score 5 · Answer 4 · edited Mar 03 '15 at 09:57

5

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

edited Mar 03 '15 at 09:57

marc_s

732,580
175
1,330
1,459

answered Jan 06 '15 at 21:19

Akki

51
1
1

score 1 · Answer 5 · edited Dec 17 '18 at 11:54

1

This will work for most of the website names :

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

edited Dec 17 '18 at 11:54

Milad Bahmanabadi

946
11
27

answered Dec 17 '18 at 11:01

Balaji

11
1

How to create a function in SQL Server

5 Answers5

Linked