在T-SQL中从给定的URL解析域名

Question

我找到了这个答案，但想进一步扩展问题，却在Stack上没有找到任何解决方案或通过谷歌搜索。

从URL SQL中提取域名子字符串

基本上，上面的链接解决了我的问题，例如使用类似于解析“www.google.com”的简单URL，并得到结果为google的答案。

我想要扩展的是，与上面链接的解决方案不适用于像'www.maps.google.com'这样的url，它只返回maps。

我想要的是从url 'www.maps.google.com'返回'google'，或者从'url 'www.test.example.com'返回'example'。

如果有人有解决方法，我将非常感激。

更新：为了更具体，我还需要对二级域进行解析等。'www.maps.google.com.au'返回'google'

这是我的Sql函数。

CREATE FUNCTION [dbo].[parseURL]  (@strURL varchar(1000))
RETURNS varchar(1000)
AS
BEGIN

IF CHARINDEX('.', REPLACE(@strURL, 'www.','')) > 0
SELECT @strURL = LEFT(REPLACE(@strURL, 'www.',''), CHARINDEX('.',REPLACE(@strURL,              'www.',''))-1)
Else
SELECT @strURL = REPLACE(@strURL, 'www.','')

RETURN @strURL
END

- Adam N

---

你想如何处理二级域名，例如 www.google.com.sg 或 bbc.co.uk？ - ig0774

是的，我刚刚更新了我的问题，包括第二级域名。我也想从 'www.google.com.sg' 返回 'google'。 - Adam N

Answer 1

我建议这样做。

DECLARE @URL nvarchar(max) = 'www.maps.google.com'

DECLARE @X xml = CONVERT(xml,'<root><part>' + REPLACE(@URL, '.','</part><part>') + '</part></root>')

SELECT [Domain] = T.c.value('.','varchar(20)')
FROM @X.nodes('/root/part[position() = last() - 1]') T(c)

这种方法是将URL转换为XML，然后使用XPath查找域名。

更新

关于二级域名，我认为唯一可靠的方法是将它们全部放在一个表中（顶级域名也应该在一个表中），然后您可以使用以下查询：

DECLARE @URL nvarchar(max) = 'www.maps.google.com'

DECLARE @X xml = CONVERT(xml,'<root><part>' + REPLACE(REVERSE(@URL), '.','</part><part>') + '</part></root>')

;WITH SplitCTE AS
(
    SELECT
        (SELECT REVERSE(T.c.value('.', 'nvarchar(256)')) FROM @X.nodes('/root/part[. = ../part[position() = 1]]') T(c)) AS TLD,
        (SELECT REVERSE(T.c.value('.', 'nvarchar(256)')) FROM @X.nodes('/root/part[. = ../part[position() = 2]]') T(c)) AS D2,
        (SELECT REVERSE(T.c.value('.', 'nvarchar(256)')) FROM @X.nodes('/root/part[. = ../part[position() = 3]]') T(c)) AS D3
)
SELECT 
    CASE
        WHEN SLD.Domain IS NULL THEN S.D2 ELSE S.D3
    END AS Domain
FROM
    SplitCTE AS S
    LEFT JOIN TLD ON TLD.Domain = S.TLD
    LEFT JOIN SLD ON SLD.Domain = S.D2

下面是我在这个例子中使用的顶级域名和二级域名表。完整的域名列表可以在这个维基中找到。请注意，一些域名是本地化的，要使用NVARCHAR。

CREATE TABLE dbo.TLD
(
    Domain nvarchar(10)
)
GO

CREATE TABLE dbo.SLD
(
    Domain nvarchar(10)
)
GO

INSERT TLD VALUES ( 'com')
INSERT TLD VALUES ( 'uk')
INSERT SLD VALUES ( 'co')

- Serge Belov

---

这是我必须要做的。我将所有TLD放在一个表中，然后编译了一个二级域名列表并将其放在另一个表中，并使用了您上面的查询。寻找二级域名列表很痛苦。感谢您对此的回答和帮助。 - Adam N

Answer 2

TLD的问题可能会严重拖慢您的代码，增加维护需求，因此我决定包含子域名。以下是我使用的内容：

ALTER FUNCTION dbo.spExtractDomainFromURL ( @strURL NVARCHAR(1000) )
RETURNS NVARCHAR(100)
AS
BEGIN
    --Strip Protocol
    SELECT  @strURL = SUBSTRING(@strURL, CHARINDEX('://', @strURL) + 3, 999)

    -- Strip www subdomain
    IF LEFT(@strURL, 4) = 'www.'
        SELECT  @strURL = SUBSTRING(@strURL, 5, 999)

    -- Strip Path
    IF CHARINDEX('/', @strURL) > 0
        SELECT  @strURL = LEFT(@strURL, CHARINDEX('/', @strURL) - 1)

    RETURN @strURL
END

- harvest316

Answer 3

我在这里所做的是截取协议“：//”后的URL子字符串。

然后，我将该字符串截成所有文本直到第一个斜杠的子字符串。

如果您需要用于查询或作为标量函数使用，则也可以在一行中完成它。

此外，最好添加一些代码来检查null或通常情况下的错误字符串。如果您想保持单行代码，请尝试使用ISNULL和NULLIF函数。

DECLARE @urlSansProtocol VARCHAR(MAX)
SET @urlSansProtocol = Substring(@url, CharIndex('://', @url)+3 ,LEN(@url))
RETURN Substring(@urlSansProtocol, 0   ,CharIndex('/',  @urlSansProtocol ))

- Garrett

---

你应该附加一些解释。 - Vladimir F Героям слава

Answer 4

可能会有更好的解决方案，但这是我得到的：

LEFT(RIGHT(@strURL, CHARINDEX('.', REVERSE(@strURL)) +  CHARINDEX('.', SUBSTRING(REVERSE(@strURL), CHARINDEX('.', REVERSE(@strURL)) + 1, LEN(@strURL))) - 1), CHARINDEX('.', SUBSTRING(REVERSE(@strURL), CHARINDEX('.', REVERSE(@strURL)) + 1, LEN(@strURL))) - 1)

我不是最擅长字符串操作的人，所以我相信可以缩短我非常长的代码行。基本上，我会使用REVERSE函数来获取最后一个句点，然后从那里开始。我的代码将引入最后两个逗号之间的字符。

请注意，使用网站www.test.co.uk将无法使用我的解决方案。我建议这只是一个起点，您需要为异常情况编写代码。

- Neil

---

这对于只有一个扩展名的内容非常有效。但如果我在问题中更加明确，比如URL为“www.maps.google.com.au”，你的答案将返回“com”。除此之外，这个答案很好用，感谢您的回复。 - Adam N

你说得完全正确。你将要面临的问题是所有可能的异常值。我建议采用Serge所建议的类似方法，将异常值放在一个表格中以供参考。我们人类可以轻松识别你需要的数据，但计算机却不能。这真的取决于你需要多么准确。另外，就可扩展性而言，我再次建议尝试理解Serge在他的帖子中所做的事情。我的方法快速而简单，但如果你需要添加更多逻辑，它可能不是最佳路线。 - Neil

Answer 5

PARSENAME函数将按照"."标记分隔开最多四个项目。Parsename从右边开始计数。如果要解析的对象名称超过四个部分，则返回NULL。

select PARSENAME ( REPLACE('www.maps.google.com.au', 'www.','')  , 3 ) 
select PARSENAME ( REPLACE('www.maps.google.com', 'www.','')  , 2 ) 

- RC_Cleland

---

这在某些情况下是有效的，但不到我需要的程度，谢谢你的回复。 - Adam N