I have a use case where i need to split strings by multiple delimiters.
| client_id |
|---|
| blah_blah |
| bleh-bleh |
select client_id ,split(client_id,'-')[0] col1` ,split(client_id,'-')[1] col2 from mytbl
returns
| Client_id | col1 | col2 |
|---|---|---|
| bleh-bleh | bleh | bleh |
I have been trying various permutations to get two delimiters in with no success.
select client_id ,split(client_id,'-'||'_')[0] col1` ,split(client_id,'-'||'_')[1] col2 from mytbl
this errors out, but what i want to return is…
| client_id | col1 | col2 |
|---|---|---|
| blah-blah | blah | blah |
| bleh_bleh | bleh | bleh |
>Solution :
You can use the regexp_split_to_array with a delimiter regex that is either a dash or an underscore:
select
client_id,
(regexp_split_to_array(client_id, '[-_]'))[1] col1,
(regexp_split_to_array(client_id, '[-_]'))[2] col2
from mytbl;
Here is a fiddle POC: https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/13029