SQL Subquery with delimiter

Question

I need to be able to split one string by the delimiter * into separate columns without including * The column y from table x looks like this:

column y
*1HS*AB*GXX*123*02*PA45*2013-08-10*
*1R1*B*GX*123*02*PA45*2013-08-10*
*1HS*B*GX*13*01*PA45*2013-08-01*
*1P*C*GXX*123*02*PA45*2013-08-10*

STRING_SPLIT is not avalible

The outcome should be this:

Column1 Column2 Column3 Column4 Column5 Column6 Column7
1HS     AB      GXX     123     2       PA45    10-08-2013
1R1     B       GX      123     2       PA45    10-08-2013
1HS     B       GX      13      1       PA45    01-08-2013
1P      C       GXX     123     2       PA45    10-08-2013

@GordonLinoff Why would you suggest this isn';t a duplicate of [How to split a comma-separated value to columns](https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns) This appears to be an exact duplicate. The OP wants to convert a delimited list into columns. — Thom A, Oct 17 '19 at 11:36

score 2 · Answer 1 · answered Oct 17 '19 at 11:20

2

will you use the below query..

select RTRIM (REGEXP_SUBSTR (column y, '[^,]*,', 1, 1), ',')    AS column 1
    ,       RTRIM (REGEXP_SUBSTR (column y, '[^,]*,', 1, 2), ',')    AS column 2
    ,       RTRIM (REGEXP_SUBSTR (column y, '[^,]*,', 1, 3), ',')    AS column 3
    ,       LTRIM (REGEXP_SUBSTR (column y, ',[^,]*', 1, 3), ',')    AS column 4
    from YOUR_TABLE

answered Oct 17 '19 at 11:20

Kiran Patil

327
1
11

`REGEXP_SUBSTR ` is not a T-SQL function – Thom A Oct 17 '19 at 11:35

score 0 · Answer 2 · answered Oct 17 '19 at 11:17

Unfortunately, string_split() does not guarantee that it preserves the ordering of the values. And, SQL Server does not offer other useful string functions.

So, I recommend using recursive CTEs for this purpose:

with t as (
      select *
      from (values ('*1HS*AB*GXX*123*02*PA45*2013-08-10*'), ('1HSB*GX*13*01*PA45*2013-08-01*')) v(str)
     ),
     cte as (
      select convert(varchar(max), null) as val, 0 as lev, convert(varchar(max), str) as rest,
             row_number() over (order by (select null)) as id
      from t
      union all
      select left(rest, charindex('*', rest) - 1), lev + 1, stuff(rest, 1, charindex('*', rest) + 1, ''), id
      from cte
      where rest <> '' and lev < 10
     )
select max(case when lev = 1 then val end) as col1,
       max(case when lev = 2 then val end) as col2,
       max(case when lev = 3 then val end) as col3,
       max(case when lev = 4 then val end) as col4,
       max(case when lev = 5 then val end) as col5,
       max(case when lev = 6 then val end) as col6,
       max(case when lev = 7 then val end) as col7
from cte
where lev > 0
group by cte.id;

Here is a db<>fiddle.

score 0 · Answer 3 · answered Oct 17 '19 at 11:38

0

Assuming you can add a table valued function to your database then Jeff Moden's string split function is the best approach I've encountered. It will allow you to maintain order as well.

Find details here

answered Oct 17 '19 at 11:38

BarneyL

1,332
8
15

SQL Subquery with delimiter

3 Answers3