How do you find a string within another string (complex)

Question

OBIEE 11g.

We have a name field that consists of first and middle initial (if one exists), followed by the last name.

each initial, if it exists, is followed by a period, hyphen, underscore, slash, or space.

Here are some of the possible entries:

J.D.Doe

J.Doe

Doe

J_D_Doe

J.D_Doe

J_D.Doe

J Doe

J/Doe

As you can see, the only real constant here, is the last name occupies the n rightmost columns of the field.

I'm trying to extract the last name, but just dont know how to write the SQL to do it.

This needs to be done in the "Edit Formula" area in an analysis.

Is it even possible to do this inside an analysis?

Thomas Dodds · Answer

Doing data cleansing in the front end of OBIEE is to seriously miss the mark ... it's an inefficient approach.

SriniVEERAVALLI · Answer

Use these with nested isnull functions for scenarios

SUBSTRING('J.D.Doe' FROM LOCATE('.','J.D.Doe',-1)+1)

SUBSTRING('J_D_Doe' FROM LOCATE('_','J_D_Doe',-1)+1)