Split string with a separator and set each part in variables in TSQL

Question

I need to split some strings with this format:

V_9_0_2_2_70_0_0_3_B

The separator is '_'.

I want that each value is stored in my variables

DECLARE   @w_grup1                char(1),

                             @w_grup2       char(1),

                             @w_grup3       varchar(10),

                             @w_grup4       char(1),

                             @w_grup5       char(1),

                             @w_grup6       varchar(10),

                             @w_grup7       char(1),               

                             @w_grup8       varchar(10),      

                             @w_grup9       char(1),               

                             @w_grup10     char(1)

How do I do that? Some suggestion?

Is there always the same amount of strings? Or does it change? — Stephan, Mar 20 '15 at 16:20

score 1 · Answer 1 · edited May 23 '17 at 12:14

1

So, something like this?

with split as
(
  select
    item = cast('' as varchar(max)),
    source = cast('V_9_0_2_2_70_0_0_3_B' as varchar(max))
  union all
  select
    item = substring(source, 1, charindex('_', source)),
    source = substring(source, charindex('_', source) + 1, 10000)
  from split
  where source > ''
)
select substring(item, 1, charindex('_', item) -1)
from split
where item > ''

from this question

edited May 23 '17 at 12:14

Community

1
1

answered Mar 20 '15 at 16:05

Matt

825
2
13
25

1

It returns: "The statement terminated. The maximum recursion 100 has been exhausted before statement completion." – Galma88 Mar 20 '15 at 16:10
It looks like, because the code above is doing it's substring based on the trailing underscore (e.g. '3_'), it's reaching the end of the string and is left with 'B' as it's Item value. Because this doesn't have an underscore, it keeps looping trying to find the underscore. Then it hits it's limit of 100 loops and throws the recursion error. It might be worth throwing in a case statement to check that the string being checked actually contains an underscore. – Aidan Mar 20 '15 at 16:43

score 1 · Accepted Answer · answered Mar 20 '15 at 17:24

I figured your best bet is a recursive CTE. Note: I didn't load the data into a table but I figure you can do that easily enough from my results. If you need anything else, let me know.

DECLARE @YourTable table (ID INT IDENTITY(1,1), String varchar(200))
INSERT @YourTable(String)
VALUES  ('V_9_0_2_2_70_0_0_3_B'),
        ('ABC_01_23_45_67_89_10_11_12_XYZ');

WITH SplitString AS
(
    SELECT  ID,
            LEFT(String,CHARINDEX('_',String)-1) AS Part,
            RIGHT(String,LEN(String)-CHARINDEX('_',String)) AS Remainder,
            1 AS RecursionCount
    FROM @YourTable
    WHERE String IS NOT NULL AND CHARINDEX('_',String) > 0

    UNION ALL

    SELECT  ID,
            LEFT(Remainder,CHARINDEX('_',Remainder)-1),
            RIGHT(Remainder,LEN(Remainder)-CHARINDEX('_',Remainder)),
            RecursionCount + 1
        FROM SplitString
        WHERE Remainder IS NOT NULL AND CHARINDEX('_',Remainder) > 0

    UNION ALL

    SELECT  ID,
            Remainder,
            null,
            recursionCount + 1
    FROM SplitString
    WHERE Remainder IS NOT NULL AND CHARINDEX('_',Remainder) = 0
)

SELECT *
FROM
(
    SELECT  CONCAT('w_grup',RecursionCount) w_grups,Part,ID
    FROM SplitString
) A
PIVOT
(
    MAX(Part) FOR w_grups IN (w_grup1,w_grup2,w_grup3,w_grup4,w_grup5,w_grup6,w_grup7,w_grup8,w_grup9,w_grup10)
) pvt

Results:

ID          w_grup1                w_grup2                w_grup3                w_grup4                w_grup5                w_grup6                w_grup7                w_grup8                w_grup9                w_grup10
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           V                      9                      0                      2                      2                      70                     0                      0                      3                      B
2           ABC                    01                     23                     45                     67                     89                     10                     11                     12                     XYZ

Split string with a separator and set each part in variables in TSQL

2 Answers2