split string into columns using comma

TKQ5Q

I have a string that contain the saleperson staff ID and their name.

I am trying to split it into 2 column like in excel.

1 column for staffID and 1 column for Name.

z32596, DanielS

z85698, BrownST

z58965, TanYUN

I tried using the string formula from out of the box, but do not know which one to use.

Can someone advise. Thank you.

I am using I am using Oracle Business Intelligence Enterprise Edition 11.1.1.7.

Gianni Ceresa

With these functions you can do what you are looking for.

LOCATE(expr1, expr2): Returns the numerical position of a character string in another character string.
LENGTH(expr): Returns the length, in number of characters, of a specified string. The length is returned excluding any trailing blank characters.

RIGHT(expr, integer): Returns a specified number of characters from the right of a string.
LEFT(expr, integer): Returns a specified number of characters from the left of a string.
or
SUBSTRING(expr FROM startPos FOR length): Creates a new string starting from a fixed number of characters into the original string.

It's simple logic: you find the location of the ',' and use that with the string manipulation functions for the job, probably by adding or subtracting 1 or 2 to cover the ',' and the space (TRIM can help with dealing with spaces as well). And the length of a string - the position of ',' gives you the length of the second part of the string.

No need to say that this transformation should be done in the DB directly instead than at the analysis level for obvious performance reasons.