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

User · Answer

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

User · Answer

I can give a small hack  you can use T-SQL function  Try this   SELECT ID  PARSENAME WebsiteName  2  FROM dbo YourTable

User · Answer

This will work for most of the website names    SELECT ID  REVERSE PARSENAME REVERSE WebsiteName   2   FROM dbo YourTable

User · Answer

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

User · Answer

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

[sql-server-2008] How to create a function in SQL Server

Examples related to sql-server-2008