如何在SQL Server中创建函数

Please help me, how to filter words in SQL using a function?

请帮帮我，如何使用函数过滤SQL中的单词？

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.

我想要的是，如何获得网站的名称。我想用这样的输出选择记录。如何删除'www。'和'.com'在记录中。

ID      |      WebsiteName
--------------------------    
1       |        yahoo

thanks for the help. :D

谢谢您的帮助。：d

4 个解决方案

#1

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

当然，这是非常有限的，因为它只会剥离www。在开头和.com在最后 - 没有别的（因此它不适用于其他主机名称，如smtp.yahoo.com和其他互联网域名，如.org，.edu，.de等）

#2

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.

这个得到“。”之间的一切。字符。请注意，这不适用于更复杂的网址，例如“www.somesite.co.uk”。理想情况下，该功能会检查“。”的实例数。字符并相应地选择子字符串。

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

#3

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

我可以给一个小黑客，你可以使用T-SQL功能。尝试这个：

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

#4

You can use stuff in place of replace for avoiding the bug that Hamlet Hakobyan has mentioned

您可以使用东西代替替换，以避免Hamlet Hakobyan提到的错误

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

#1