need regexp_extract help, beginner

Question

I have string column "49b8b35e-b62c-4a42-9d73-192d131d127a,03c8a7e0-5153-11ec-873a-0242ac11000a,eec8aee4-0500-4940-b319-15924cc2d248"

this string column has 3 values separate by ",". (value1,value2,value3). there is no guarantees that vaule2 and value3 need to be present always. but value1 does always.

so i need to split this single column into 3 different columns keeping the above conditions of their existence in mind and they need to be separated by before "," into a new column.

i have so far wrote as

select regexp_extract('49b8b35e-b62c-4a42-9d73-192d131d127a,03c8a7e0-5153-11ec-873a-0242ac11000a,eec8aee4-0500-4940-b319-15924cc2d248', '^(.+?),') as value1

but after that my logic and thinking giving errors and no luck to me.

Answer 1

You can use split() function, it returns array and you can access elements using array index []:

select split("49b8b35e-b62c-4a42-9d73-192d131d127a,03c8a7e0-5153-11ec-873a-0242ac11000a,eec8aee4-0500-4940-b319-15924cc2d248",',')[0] --to get first element

Use [0] for first element, [1] for second element and [2] for third, If some elements are absent and no commas (for example split() returned array of size=1 and you want 2nd element) it will return NULL, if commas exist but empty string in between them, it will return empty, so adjust your logic accordingly.

And if you prefer regexp_extract function:

 regexp_extract('49b8b35e-b62c-4a42-9d73-192d131d127a,03c8a7e0-5153-11ec-873a-0242ac11000a,eec8aee4-0500-4940-b319-15924cc2d248', '^([^,]+),*([^,]*),*([^,]*)',1) as value1,
 regexp_extract('49b8b35e-b62c-4a42-9d73-192d131d127a,03c8a7e0-5153-11ec-873a-0242ac11000a,eec8aee4-0500-4940-b319-15924cc2d248', '^([^,]+),*([^,]*),*([^,]*)',2) as value2,
 regexp_extract('49b8b35e-b62c-4a42-9d73-192d131d127a,03c8a7e0-5153-11ec-873a-0242ac11000a,eec8aee4-0500-4940-b319-15924cc2d248', '^([^,]+),*([^,]*),*([^,]*)',3) as value3

The same pattern is used, only the group number is different. If element is absent, regexp_extract returns empty string.