cancel
Showing results for 
Search instead for 
Did you mean: 

How to split a comma separated String into multiple rows in HANA SQL

chandan_praharaj
Contributor
0 Kudos

Hi Experts,

I have a requirement in HANA where in I have data like below.

404856883FA212,FA214,
404856883FA211,FA213,FA204
404856883FA203,FA195,FA177
404856883FA204,FA177,

I need to convert to as below.

404856883FA212
404856883FA214
404856883FA211
404856883FA213
404856883FA204
404856883FA203
404856883FA195
404856883FA177
404856883FA204
404856883FA177

Can I achieve it in HANA SQL?

In Data services we can achieve, can we achieve it in HANA, without going into Procedure logic. As I have to bring the First table data virtually from Teradata.

How to split a comma separated String into multiple rows via word_ext - Enterprise Information Manag...

Please help.

Regards,

Chandan

Accepted Solutions (1)

Accepted Solutions (1)

lbreddemann
Active Contributor

Nope, there's no SQL function for that.

You probably could write a SQL Script function for that.

chandan_praharaj
Contributor
0 Kudos

Thanks Lars for your response.

Based on my requirement, I tried using REGEXPR as I am in SP10. to split it to 3 columns. And some how able to achieve the functionality.

For REGEXPR also there was no adequate reference on SCN. I will create a separate blog for that.

below is the dummy logic, I thought of using.

Hope this will help for the community to use REGEXPR


SELECT SUBSTR_REGEXPR('([[:alnum:][:punct:]]{5})([[:alnum:][:punct:]]{6})([[:alnum:][:punct:]]{6})' IN

'FA168,FA162,FA165,' GROUP 1) "substring_regexpr" FROM DUMMY;

  SELECT SUBSTR_REGEXPR('([[:alnum:][:punct:]]{6})([[:alnum:][:punct:]]{5})([[:alnum:][:punct:]]{6})' IN

'FA168,FA162,FA165,' GROUP 2) "substring_regexpr" FROM DUMMY;

   SELECT SUBSTR_REGEXPR('([[:alnum:][:punct:]]{6})([[:alnum:][:punct:]]{6})([[:alnum:][:punct:]]{5})' IN

'FA168,FA162,FA165,' GROUP 3) "substring_regexpr" FROM DUMMY;

Regards,

Chandan

lbreddemann
Active Contributor
0 Kudos

Splitting the string by delimiter is not too difficult. The heavy lifting here is with creating new rows from the split strings.

For that I don't see an elegant or straight forward way.

Maybe some Union or outer join approach could work here.

Answers (2)

Answers (2)

oliver_sviszt
Explorer
0 Kudos
dubravko_katulic2
Discoverer
0 Kudos

First post after a Year !!!!    Sorry Chandan if it is not usefull.

@Lars : Seem the rule still works