New post
Question
· May 5, 2021

STRING_SPLIT equivalent in Cache SQL

#Caché

I'm looking for a SQL function equivalent for SQL server STRING_SPLIT.   I have records like this:

join_to_tx_history (key), ss_note_multi_dict_1_value (varchar)

64559.001 Assistant Principal, Case Worker, Client, Dad, Daughter, Mentor, Mom, Other, Principal, Psychiatrist, School Counselor, Teacher

and I like the field ss_note_multi_dict_1_value split to rows for each comma delimited value.  So I can get this:

64559.001    Assistant Principal
64559.001    Case Worker
64559.001    Client
64559.001    Dad
64559.001    Daughter
64559.001    Mentor
64559.001    Mom
64559.001    Other
64559.001    Principal
64559.001    Psychiatrist
64559.001    School Counselor
64559.001    Teacher

Any ideas in Cache SQL?

Product version: Caché 2017.1
Discussion (2)0
Log in or sign up to continue
Georg Rensch · May 7, 2021

Hallo,
i can think of two ways:
1)
you need a table with a field that has the numbers from 1 to the maximum numbers of pieces your list can have.
If ss_note_multi_dict_1_value can consist of 10 values you need a table with 10 or more entries (one entry for each number from 1 to n).
Let's say this table is called dummy and the field counter, than your query would be
select $piece(a.ss_note_multi_dict_1_value,',',d.counter) ,a.join_to_tx_history
from your_table a cross join dummy d
where (the condition of your select) and d.counter<=10 and $piece(a.ss_note_multi_dict_1_value,',',d.counter) is not null

2)
select $piece(a.ss_note_multi_dict_1_value,',',1),join_to_tx_history
from your_table where (the condition of your select) and $piece(a.ss_note_multi_dict_1_value,',',1) is not null
union
select $piece(a.ss_note_multi_dict_1_value,',',2),join_to_tx_history
from your_table where (the condition of your select) and $piece(a.ss_note_multi_dict_1_value,',',2) is not null
union
select $piece(a.ss_note_multi_dict_1_value,',',3),join_to_tx_history
from your_table where (the condition of your select) and $piece(a.ss_note_multi_dict_1_value,',',3) is not null
...
I hope this helps.

1 0