How to extract hashtags from a string in T-SQL

Question

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

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

Answer 1

Try it like this

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)

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:

Try it like this

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,'')

The result

#want,#hastag,#delhi,#Traffic