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?

Use string functions from sql server like`charindex`, `replace`,`substring` — Mahesh, Feb 27 '15 at 06:14
Replace will not help because all the time I get another numbers. Substring could be better but he cut only first text for example: substring(TEXT, CHARINDEX('#',TEXT)+1,2500) — Rafał Developer, Feb 27 '15 at 06:17
@Rafael-JuniorMVCDeveloper, Replace is helpful in your case, because you can use it to replace ";" with "#" to be able to use only one char as delimiter ;) — Maciej Los, Feb 27 '15 at 06:24
possible duplicate of [Tsql split string](http://stackoverflow.com/questions/10914576/tsql-split-string) — WorkSmarter, Feb 27 '15 at 06:31

Maciej Los · Accepted Answer · 2015-02-27T06:50:08.907

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

Dzięki za pomoc :) Thanks for your time. – Rafał Developer Feb 27 '15 at 06:37 — Rafał Developer, Feb 27 '15 at 06:37

score 5 · Answer 2 · edited Jan 18 '21 at 12:30

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

Cut Text Between Two Words

2 Answers2