本文介绍了如何从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中的字符串中提取主题标签的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!