Find Last name from a lastname,first name in universe designer

[Former Member]

If my name is in format Pathak,Raja, i want to find my last name as Pathak and first name as Raja in universe designer only.

I know how it can be done in report level but since pos() and left/right function is not available in universe designer am looking for alternate solution

[Former Member]

Use this:

SUBSTR(table.column_name,1,instr(table.column_name,',',1,1)-1)

It creates a substring from the first character to the character before the first comma in the field

[Former Member]

For first name, use this:

SUBSTR(table.column_name,instr(table.column_name,',',1,1)+1,len(table.column_name)-instr(table.column_name,',',1,1))

This will create a substring from the first character after the comma through to the end of the string, so if you have Woods,Tiger this will just return Tiger

If you have Woods, Tiger with a space after the comma, then you will need this:

SUBSTR(table.column_name,instr(table.column_name,',',1,1)+2,len(table.column_name)-1-instr(table.column_name,',',1,1))

HOWEVER!!!!

All this falls down with names where last name has a comma in it. Thankfully, this tends not to happen with the apostrophe a far more likely character in both eastern and western names.

[Former Member]

Create two dimensions in the business layer:

LastName=

mid(name_field,1,instr(name_field,',')-1)

FirstName=

mid(name_field,instr(name_field,',')+1)

-Ankush

[Former Member]

what is your backend database?

Thanks,

Swapnil

[Former Member]

Oracle 10 is the database

[Former Member]

Create two objects on universe like

For last name :

last_name = substr('abc,pqr',instr('abc,pqr',',',1,1)+1)
this will return pqr

For First name:

first_name = substr('abc,pqr',1,instr('abc,pqr',',',1,1)-1)

this will return abc

I hope this will help.

Thanks,

Swapnil

[Former Member]

Last name is not working. Can you please check?

[Former Member]

Please try below solution for last name

last_name  = replace(substr('abc,pqr,',instr('abc,pqr,',',',1,1)+1),',','')

[amitrathi239]

Hi,

See attach link.

How to split column in to two objects in Universe level?

Amit

[Former Member]

CHARINDEX() is not applicable in universe designer 😞