How to split comma separated string into columns in OBIEE

3317169

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 - TBD
enclosure ; Solar - TBD; inspector - TBD ; Shopkeeper

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

                                              

Passenger1	Passenger2	Passenger3	Additional Passenger
Solar enclosure specialist 1 - TBD	Solar enclosure specialist 2 - TBD	inspector - TBD	NULL
enclosure	Solar - TBD	inspector - TBD	Shopkeeper

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

Many thanks

Titas

Joel

You can do this either at ETL level (preferred ) or in OBIEE. Firstly, will you only have 4 columns? Is there a chance that there can be more? This will have an impact on the logic.

3317169

Hi Joel,

Good to see you, . How are you?

This needs to be done in OBIEE level actually. The four columns are fixed. Kindly help how this can be done.

Thanks

Titas

Gianni Ceresa

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.

Joel

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

P G

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…