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:

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.

CodePudding user response：

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,
       . . .

CodePudding user response：

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|
*/