Splitting columns over multiple rows - String_Split does not work

Question

I have the following code:

select *
from (
    select aTable.cas_id,  aTable.udv_string
    from bTable, aTable
    where bTable.field_id = '50'
    and bTable.field_id = aTable.field_id
) infotable

This gives the following output:

cas_id    udv_string
582       2    4
615       2
276       23
453       2    4
1004      2    4

As you can see, three rows have multiple values which are space separated. I have tried to use 'split_string' but this doesn't work for me. Can someone please help? I have also searched and tried to apply the solutions on your stackoverflow sites regarding this problem to no avail.

I would like the output to be:

cas_id   udv_string
582      2
582      4
615      2
276      23
453      2
453      4
1004     2
1004     4

Tip of today: Switch to modern, explicit `JOIN` syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed. — jarlh, Apr 22 '20 at 07:24
What is the problem with `STRING_SPLIT()` (not `split_string`)? — Zhorov, Apr 22 '20 at 07:26
I meant string_split() won't work. The object is not recognised — jackie, Apr 22 '20 at 08:09

score 0 · Accepted Answer · answered Apr 22 '20 at 08:48

0

If you are working with SQL Server 16 + then you can use string_split() :

select a.cas_id, aa.value as udv_string
from aTable a inner join
     bTable b
     on b.field_id = a.field_id cross apply
     string_split(a.udv_string, '  ') aa
where a.field_id = 50;

Note : I have used standar explicit join syntax instead of comma separated.

For older version you can reffer this.

answered Apr 22 '20 at 08:48

Yogesh Sharma

49,870
5
26
52

Thanks for the help Yogesh! I really appreciate it. That took me hours and I could not work out why string_split() would not work – jackie Apr 22 '20 at 22:14

Splitting columns over multiple rows - String_Split does not work

1 Answers1