如何使用T-SQL從HTML內容中獲取所有PDF鏈接

我正在嘗試從包含 HTML 的字串列中檢索所有 PDF 鏈接。

一列的示例文本是：

<p>text here <a href="example.com/abc.pdf">link</a>
 some other text <a href="example.com">home</a>
 <a href="www.example.com/abc123.pdf">link 2</a></p>

我需要所有帶有 .pdf 擴展名的鏈接。

我已經嘗試過這樣的功能

ALTER function [dbo].[GetLinks] (@t nvarchar(max))
    returns @Links table (link nvarchar(max))
as
begin
    declare @strtpos int
    set @strtpos=100
    declare @endpos int 
    declare @lnk nvarchar(max)
    while @strtpos > 6
    begin
        set @strtpos = PATINDEX('%href="%', @t) 6
        if @strtpos>6 begin
            --set @endpos = CHARINDEX ('"',@t,@strtpos 1)
            set @endpos = PATINDEX('%.pdf"%',@t) 4
            if @endpos>0 begin
                set @lnk = substring(@t ,@strtpos, @endpos - @strtpos)
                set @strtpos = PATINDEX('%href="%', @lnk) 6
                set @t= RIGHT (@t, len(@t) - @endpos)

                insert @Links values(@lnk)
            end
        end
    end 

    return
end

并從 SQL Server 呼叫此函式，如下所示：

select top 1 * from dbo.GetLinks(' <p>text here <a href="example.com/abc.pdf">link</a>
     some other text <a href="example.com">home</a>
     <a href="www.example.com/abc123.pdf">link 2</a></p>')

這僅在我匹配 CHAR 時回傳第一個鏈接，但是當我匹配字串“.pdf”時它回傳長字串。如果我做錯了什么或需要為此改變方法，請告訴我。

uj5u.com熱心網友回復：

如果您的 html 列可以像您的示例所建議的那樣轉換為 XML，您可以使用 XML 資料型別方法決議 T-SQL 中的 href 值：

CREATE FUNCTION dbo.GetLinks (@t xml)
    RETURNS @Links TABLE (link nvarchar(max))
AS
BEGIN

    INSERT @Links 
    SELECT
        AnchorTag.value('@href', 'nvarchar(MAX)') AS link
    FROM @t.nodes('//a') AS AnchorTags(AnchorTag);

RETURN;
END;
GO

同樣的方法可以用于行內 TVF：

CREATE FUNCTION dbo.GetLinks (@t xml)
    RETURNS TABLE
AS
RETURN (
    SELECT
        AnchorTag.value('@href', 'nvarchar(MAX)') AS link
    FROM @t.nodes('//a') AS AnchorTags(AnchorTag)
);
GO

uj5u.com熱心網友回復：

Xquery 運算式可以簡單地做到

DECLARE @html xml = '<p>text here <a href="example.com/abc.pdf">link<b v="3">ok</b></a>some other text <a href="example.com">home</a><a title="er">kj</a><a href="www.example.com/abc123.pdf">link 2</a></p>'

select [pdfLink] = a.value('@href','varchar(max)')
from @html.nodes('//a[@href[contains(., ".pdf")]]') c(a) 

uj5u.com熱心網友回復：

如果由于某種原因您無法將 html 轉換為 xml，您仍然可以使用常規字串操作來完成此操作，盡管它并不漂亮。

該解決方案（具有諷刺意味的是）利用基于 xml 的字串拆分器來允許多字符分隔符，然后進一步過濾其輸出以僅回傳.pdf鏈接：

create or alter function [dbo].[fn_StringSplitXML]
(
    @str       varchar(max) = ''    -- String to split.
   ,@Delimiter varchar(10) = ','    -- Delimiting value to split on.
   ,@num       int = null           -- Which value to return.
)
returns table
as
return
select rn
      ,item
from(select rn = row_number() over(order by(select null))
           ,item = ltrim(rtrim(n.i.value('(./text())[1]','varchar(max)')))
     from(select x = cast('<x>' replace(@str,@Delimiter,'</x><x>') '</x>' as xml).query('.')) as s
        cross apply s.x.nodes('x') as n(i)
    ) as a
where rn = @num
    or @num is null
;

declare @html varchar(1000) =
'<p>text here <a href="example.com/abc.pdf">link</a>
 some other text <a href="example.com">home</a>
 <a href="www.example.com/abc123.pdf">link 2</a></p>
 <input type="text" name="self closed tag" />
 <b>some more text</b>
';


select left(s.item
           ,patindex('%.pdf%',s.item) 3
           ) as link
from dbo.fn_StringSplitXML(replace(replace(@html
                                          ,'>'
                                          ,''
                                          )
                                  ,'<'
                                  ,''
                                  )
                          ,'href="'
                          ,null
                          ) as s
where patindex('%.pdf%',s.item) > 0;

輸出

uj5u.com熱心網友回復：

如果您使用的是 SQL Server 2016 ，則可以使用STRING_SPLIT。

DECLARE @string VARCHAR(8000) = '
<p>text here <a href="example.com/abc.pdf">link</a>
 some other text <a href="example.com">home</a>
 <a href="www.example.com/abc123.pdf">link 2</a></p>';

SELECT TheUrl = split.value
FROM   STRING_SPLIT(@string,'"') AS split
WHERE  split.value LIKE '%.pdf';

回傳：

TheUrl
---------------------------
example.com/abc.pdf
www.example.com/abc123.pdf

標籤：sql sql-server

上一篇：如何進行查詢以獲取比率？

下一篇：帶有OUTPUT的SQLAlchemyINSERTstmt回傳多行