CODEWITHSUNDEEP
t-sqlstored-proceduressplit
Galma88
Galma88

Reputation: 2546

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

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?

Upvotes: 0

Views: 59

Answers (2)

Stephan
Stephan

Reputation: 6018

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

Upvotes: 1

Matt
Matt

Reputation: 835

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

Upvotes: 1

Related Questions