How to split comma separated string into columns in OBIEE

Question

Hi All,

I have a requirement in OBIEE where a column value (semicolon separated string) needs to be splitted into four different columns like below:

In one existing report, I have one column where the value looks like below:

Column1 
Solar enclosure specialist 1 - TBD; Solar enclosure specialist 2 - TBD; inspector - TBDenclosure ; Solar - TBD; inspector - TBD ; Shopkeeper

Now this column value (semicolon separated) needs to be seperated into four columns like below:

Passenger1Passenger2
Passenger3
Additional Passenger
Solar enclosure specialist 1 - TBDSolar enclosure specialist 2 - TBDinspector - TBDNULLenclosureSolar - TBDinspector - TBDShopkeeper

Could anyone kindly help me how I can do this in OBIEE Analytics. Please note, I am using OBIEE 12c version.

Many thanks

Titas

P G · Answer

Hi,

Check this link

https://oraclebizint.wordpress.com/2009/06/04/oracle-bi-ee-10-1-3-4-1-handling-complex-string-manipulations-using-regula…

Joel Acha · Answer

As @Gianni Ceresa mentioned, you just need to make use a combination of the LOCATEN & SUBSTRING functions to achieve this.

Gianni Ceresa · Answer

OBIEE has functions to locate the position of a string into another and to get a substring from a string. Just combine these 2 as much as needed to get the position of the 3 " ; " and then use substring to extract the value.