Split row string into columns

Question

I have conversion column in some table which contains next strings:

9076635012/DOVE/603524/source:unilever/campaign:L-Unilever_Tresemme_SMM_jan-feb2021/content:video_18_Stories/medium:display
707663501/AXE/60364/source:unilever/campaign:AXE/term:instagram/medium:display

I want to get next result:

id	brand	code	source	campaign	content	term	medium
9076635012	DOVE	603524	unilever	L-Unilever_Tresemme_SMM_jan-feb2021	video_18_Stories	NULL	display
707663501	AXE	60364	unilever	AXE	NULL	instagram	display

I can easily split id, brand, code:

SELECT
split_part(a."conversion", '/', 1)  as id,
split_part(a."conversion", '/', 2) as brand,
split_part(a."conversion", '/', 3) as code

How to split next parts? Pattern is /attribute_name: like /content: and consider that one of the part may not exist in 1st row, but can exist in 2nd row. And if it's possible it doesn't have to be order-oriented. campaign can be at the begining (right after code) or at the end. id, brand, code always exist and always have same order.

I tried like this:

substring(split_part(a."conversion", '/source:', 2),'[^\/]') as source

But get u as source.

Stefanov.sm · Accepted Answer · 2021-09-22 17:05:53Z

Use the regexp version of substring and anchor words in order to be position independent. Here is an illustration with a CTE.

with t(conversation) as
(
 values 
 ('9076635012/DOVE/603524/source:unilever/campaign:L-Unilever_Tresemme_SMM_jan-feb2021/content:video_18_Stories/medium:display'), 
 ('707663501/AXE/60364/source:unilever/campaign:AXE/term:instagram/medium:display')
)
select 
    split_part(conversation, '/', 1)  as id,
    split_part(conversation, '/', 2)  as brand,
    split_part(conversation, '/', 3)  as code,
    substring(conversation from '/source:([^/]+)/?')   as source,   
    substring(conversation from '/campaign:([^/]+)/?') as campaign,   
    substring(conversation from '/content:([^/]+)/?')  as "content",   
    substring(conversation from '/term:([^/]+)/?')     as term,   
    substring(conversation from '/medium:([^/]+)/?')   as medium   
from t;

/* Result:
id        |brand|code  |source  |campaign                           |content         |term     |medium |
----------+-----+------+--------+-----------------------------------+----------------+---------+-------+
9076635012|DOVE |603524|unilever|L-Unilever_Tresemme_SMM_jan-feb2021|video_18_Stories|         |display|
707663501 |AXE  |60364 |unilever|AXE                                |                |instagram|display|
*/

Gordon Linoff · Accepted Answer · 2021-09-22 15:18:43Z

1

Just split again:

SELECT split_part(a."conversion", '/', 1)  as id,
       split_part(a."conversion", '/', 2) as brand,
       split_part(a."conversion", '/', 3) as code,
       split_part(split_part(a."conversion", '/', 4), ':', 1) as source,
       . . .

answered Sep 22, 2021 at 15:18

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

2 Comments

takotsubo Over a year ago

What about ordering? F.e. source can be at 4 position in one string, and at 6 position in second string. Can I handle this case?

Gordon Linoff Over a year ago

@hsadik . . . I don't see that in your question.

Collectives™ on Stack Overflow

Split row string into columns

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related