Is it possible to get all sub-strings within particular characters, '#image' and '#' without using loop or what is the best way
declare @str nvarchar(max)='get all substrings #image:header# #image:footer#'
expected result
Image
header
footer
-
Not sure, but think that it can be done using xpathAK47– AK472014-05-15 12:04:39 +00:00Commented May 15, 2014 at 12:04
Add a comment
|
2 Answers
get all substrings #image:header# #image:footer#
Replace #image: with the start-tag <X>.
get all substrings <X>header# <X>footer#
Replace # with the end-tag </X>
get all substrings <X>header</X> <X>footer</X>
Cast to XML and shred on X.
declare @str nvarchar(max)='get all substrings #image:header# #image:footer#'
select T2.X.value('text()[1]', 'nvarchar(20)') as [image]
from (select cast(replace(replace((select @str for xml path('')), '#image:', '<X>'), '#', '</X>') as xml).query('.')) as T1(X)
cross apply T1.X.nodes('X') as T2(X)
Result:
image
--------------------
header
footer
I got the solution from here by using CTE recursively to strip out the string
declare @str nvarchar(100)='get all substrings #image:header# #image:footer# between specific characters'
declare @imageTokenStart nvarchar(8)='#image:'
declare @imageTokenEnd nvarchar(1)='#'
;WITH cte(msg,token)
as
(
SELECT RIGHT(msg,(LEN(msg)- CHARINDEX(@imageTokenEnd, msg, CHARINDEX(@imageTokenStart, msg, 0) + 1)))
,SUBSTRING(msg, CHARINDEX(@imageTokenStart, msg, 0) + LEN(@imageTokenStart)
, CHARINDEX(@imageTokenEnd, msg, CHARINDEX(@imageTokenStart, msg, 0) + 1) - (
CHARINDEX(@imageTokenStart, msg, 0) + LEN(@imageTokenStart)))
FROM (SELECT @str as msg) tmp
WHERE CHARINDEX(@imageTokenStart,msg,0)>0
UNION ALL
SELECT RIGHT(msg,(LEN(msg)- CHARINDEX(@imageTokenEnd,msg,CHARINDEX(@imageTokenStart, msg, 0) + 1)))
,SUBSTRING(msg, CHARINDEX(@imageTokenStart, msg, 0) + LEN(@imageTokenStart)
, CHARINDEX(@imageTokenEnd, msg,
CHARINDEX(@imageTokenStart, msg, 0) + 1) - (CHARINDEX(@imageTokenStart, msg, 0) + LEN(@imageTokenStart)))
FROM cte
WHERE CHARINDEX(@imageTokenStart,msg,0)>0
)
SELECT token FROM cte
Result
header
footer
