STRING_SPLIT equivalent in Cache SQL
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
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.
Thanks Georg, I like option 2. I have a maximum of 12 values possible so I was able to write a general query (with 12 unions) to handle all possible records. I'm surprised how good the performance of $piece in combination with the unions are. I could limit or even eliminate the "(condition of your select)" to make a general in-line view to suite broad queries.