如何从T-SQL中的字符串中提取主题标签 [英] How to extract hashtags from a string in T-SQL

问题描述

Declare @text='i #want to extract all #hastag out of this string, #delhi #Traffic'

所需的输出将在字符串中:"#want,#hastag,#del#Traffic"或表中.

Desired output would be in string: "#want,#hastag,#del#Traffic" or table.

推荐答案

像这样尝试

Declare @text VARCHAR(100)='i #want to extract all #hastag out of this string, #delhi #Traffic';

WITH Casted(ToXml) AS (SELECT CAST('<x>' + REPLACE((SELECT @text AS [*] FOR XML PATH('')),' ','</x><x>') + '</x>' AS XML))
SELECT SUBSTRING(x.value('.','nvarchar(max)'),2,1000)
FROM Casted
CROSS APPLY ToXml.nodes('x[substring((./text())[1],1,1)="#"]') AS A(x)

结果(我已经剪掉了#，如果需要的话，只需剪掉外面的SUBSTRING)

The result (I've cut away the #, just take away the outer SUBSTRING if you need it)

want
hastag
delhi
Traffic

或作为放置在预期输出中的字符串:

Or as the string you placed as expected output:

尝试一下

Declare @text VARCHAR(100)='i #want to extract all #hastag out of this string, #delhi #Traffic';

WITH Casted(ToXml) AS (SELECT CAST('<x>' + REPLACE((SELECT @text AS [*] FOR XML PATH('')),' ','</x><x>') + '</x>' AS XML))
SELECT STUFF(
(
SELECT ','+x.value('.','nvarchar(max)')
FROM Casted
CROSS APPLY ToXml.nodes('x[substring((./text())[1],1,1)="#"]') AS A(x)
FOR XML PATH(''),TYPE
).value('.','nvarchar(max)'),1,1,'')

结果

#want,#hastag,#delhi,#Traffic

这篇关于如何从T-SQL中的字符串中提取主题标签的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！