Cut Text Between Two Words

Question

I would like get only this:

000020004641,000020006307,000020001441

Now I get this:

000020004641;1003000020006307#000020006307;1003000020001441#000020001441

In my opinion I have to cut text between ; and this # but I don`t know how.

Have you got any idea how to do it?

Answer 1

Sample query:

DECLARE @text VARCHAR(150) = '000020004641;1003000020006307#000020006307;1003000020001441#000020001441'

;WITH CTE AS
(
    --initial part
    SELECT 1 AS MyCounter, LEFT(REPLACE(@text, '#', ';'), CHARINDEX(';',REPLACE(@text, '#', ';'))-1) AS MyText, 
        RIGHT(REPLACE(@text, '#', ';'), LEN(REPLACE(@text, '#', ';')) - CHARINDEX(';',REPLACE(@text, '#', ';'))) AS Remainder
    WHERE CHARINDEX(';',REPLACE(@text, '#', ';'))>0
    --recursive part
    UNION ALL
    SELECT MyCounter +1 AS MyCounter, LEFT(Remainder, CHARINDEX(';',Remainder)-1) AS MyText, 
        RIGHT(Remainder, LEN(Remainder) - CHARINDEX(';',Remainder)) AS Remainder
    FROM CTE
    WHERE CHARINDEX(';',Remainder)>0
    UNION ALL
    SELECT MyCounter +1 AS MyCounter, Remainder AS MyText, NULL AS Remainder
    FROM CTE
    WHERE CHARINDEX(';',Remainder)=0
)
SELECT *
FROM CTE

Result:

MyCounter   MyText  Remainder
1   000020004641    1003000020006307;000020006307;1003000020001441;000020001441
2   1003000020006307    000020006307;1003000020001441;000020001441
3   000020006307    1003000020001441;000020001441
4   1003000020001441    000020001441
5   000020001441    NULL

[EDIT]

You're MVC developer. So, my next suggestion is to use Linq:

string aText = @"000020004641;1003000020006307#000020006307;1003000020001441#000020001441";

var qry = aText.Split(new char[]{';','#'}).Select(x=>x);

Cheers
Maciej

Answer 2

You can take your string value

000020004641;1003000020006307#000020006307;1003000020001441#000020001441

and change it into XML by replacing the delimiters with start tags and end tags

000020004641<X>1003000020006307</X>000020006307<X>1003000020001441</X>000020001441

That is a mixed content XML fragment where the node values you are looking is located as separate text nodes on the root level.

Then you can shred the XML on the text nodes and concatenate to a string using the for xml path trick.

declare @S varchar(100) = '000020004641;1003000020006307#000020006307;1003000020001441#000020001441';

set @S = replace(@S, ';', '<X>');
set @S = replace(@S, '#', '</X>');

declare @X xml = cast(@S as xml);

select stuff((
            select ','+T.X.value('.', 'varchar(100)')
            from @X.nodes('text()') as T(X)
            for xml path('')
            ), 1, 1, '');

Data Explorer