Retrieve String Between Two Delimiters for Multiple Occurences SQL Server

Question

How can i get the result set as TESTING1,TESTING2 from below

DECLARE @MyString varchar(256) = '$I10~TESTING1$XYZ$I10~TESTING2$~'

Basically i need to get all the substrings which are in between $I10~ and $

Answer 1

See inline comments for an overview of what's going on:

DECLARE @MyString varchar(256) = '$I10~TESTING1$XYZ$I10~TESTING2$~'
, @pre char(5) = '$I10~' --this appears before the string we want
, @post char(1) = '$' --this appears after it
select 
--take part of
substring( 
    --the input string
    @MyString     
    --starting from the first pre-delimiter (but add on the length of the delimeter so we exclude the delimeter itself)
    ,charindex(@pre,@MyString) + len(@pre) 
    --and ending at the first post-delimiter to appear after the first pre-delimeter
    , charindex(@post,@MyString,charindex(@pre,@MyString) + len(@pre)) - (charindex(@pre,@MyString) + len(@pre))
) 
,
--for the second result do the same as above
substring(
    @MyString
    --only now we're looking for the second pre-delimiter (aka the first after the first)
    ,charindex(@pre,@MyString,charindex(@pre,@MyString) + len(@pre)) + len(@pre)
    --and the second post-delimiter
    ,charindex(@post,@MyString,charindex(@pre,@MyString,charindex(@pre,@MyString) + len(@pre)) + len(@pre)) - (charindex(@pre,@MyString,charindex(@pre,@MyString) + len(@pre)) + len(@pre))
) 

NB: this assumes that the pre delimiter doesn't appear between the pre and post delimiter; if it did that could get confusing / we'd need to determine the desired behaviour.

substring(@stringToBreakApart, @indexOfFirstCharacterInSubstring, @lengthOfResultingString) - returns a segment of the original string.

charindex(@stringToFind, @stringToSearch, @indexOfFirstCharacterToLookAt) - returns the index of the first character of a given substring within a given string.

len(@stringToAnalyze) - returns the number of characters (length) of the given string.

Update

Per comments, here's how you could return a single column which splits the string on the delimiters (ignoring anything not between pre and post) and then joins the results to form a comma delimited field.

DECLARE @MyString varchar(256) = '$I10~TESTING1$XYZ$I10~TESTING2$~$I10~TESTING3$...'
, @pre char(5) = '$I10~' --this appears before the string we want
, @post char(1) = '$' --this appears after it
, @newDelim char(1) = ','
;with cte(indx, firstCharIndex, lastCharIndex) as 
(
    select 0 
    , charindex(@pre,@MyString) + len(@pre) 
    , charindex(@post,@MyString,charindex(@pre,@MyString) + len(@pre)) 

    union all

    select indx + 1
    , charindex(@pre,@MyString, lastCharIndex + len(@post)) + len(@pre) 
    , charindex(@post,@MyString,charindex(@pre,@MyString, lastCharIndex + len(@post)) + len(@pre)) 
    from cte
    where charindex(@pre,@MyString, lastCharIndex + len(@post)) > 0
)
, cte2 (substr, indx ) as 
(
    select cast(substring(@MyString, firstCharIndex, lastCharIndex - firstCharIndex) as nvarchar(max)) 
    , indx
    from cte 
    where indx = (select max(indx) from cte)

    union all

    select substring(@MyString, firstCharIndex, lastCharIndex - firstCharIndex) + @newDelim + cte2.substr
    , cte.indx
    from cte
    inner join cte2 on cte2.indx = cte.indx + 1 

)
select * from cte2 where indx = 0