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

Question

我找到了这个答案，但想扩展这个问题，但在堆栈上或通过搜索谷歌找不到任何解决方案。

Substring domainname from URL SQL

基本上，上面的链接通过一个简单的 URL 解决了我的问题，例如用 google 的结果解析“www.google.com”。

我想要扩展的是上面链接中的解决方案对仅返回 map 的“www.maps.google.com”之类的网址没有帮助。

我想让它从网址“www.maps.google.com”返回“google”，或者从“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

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

我用于此示例的 TLD/SLD 表如下。完整的域列表在此 wiki 。请小心使用 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')