CODEWITHSUNDEEP
postgresql
voltdev
voltdev

Reputation: 308

How to regexp split to table regexed splitted table

I have table with combined string and I want to split it to first parts. I have results from query with regexped split to table.

Now i have split from this: 1:9,5:4,4:8,6:9,3:9,2:5,7:8,34:8,24:6 to this table:

campaign_skill
----------------
1:9
5:4
4:8
6:9
3:9
2:5
7:8
34:8
24:6

with this expression: 

select * 
from regexp_split_to_table((select user_skill from users where user_token = 'ded8ab43-efe2-4aea-894d-511ed3505261'), E'[\\s,]+') as campaign_skill

How to split actual results to tables like this:

campaign | skill
---------|------
1        | 9
5        | 4
4        | 8
6        | 9
3        | 9
2        | 5
7        | 8
34       | 8
24       | 6

Upvotes: 0

Views: 180

Answers (1)

user330315
user330315

Reputation:

You can use split_part() for that. 

select split_part(t.campaign_skill, ':', 1) as campaign,
       split_part(t.campaign_skill, ':', 2) as skill
from users u, 
     regexp_split_to_table(u.user_skill, E'[\\s,]+') as t(campaign_skill)
where u.user_token = 'ded8ab43-efe2-4aea-894d-511ed3505261';

Upvotes: 1

Related Questions