using substring function in the universe

building semantic layer
1

What’s the correct syntax of using substring function in the universe.

Need to convert a sting to something like this,
Example: Smith,John --> John Smith

Used the following function to create a object to grab the last name but its not parsing .
substring((DB2.Site.Manager) ,Pos((DB2.Site.Manager), ", ")+1 ,Length(DB2.Site.Manager))

The enviornment is XIR2 and DB2 is the database.

Thank you in advance…
BO Worker

BOWorker (BOB member since 2007-11-17)

2

Hi,

First of all, I am not an expert on DB2.

However, I think that the problem in your formula is not the SUBSTR() function but the POS() function. POS() is a BO report level function. Very likely, it’s not a function available in DB2 database. You might use LOCATE() function instead:

Marek Chladny :slovakia: (BOB member since 2003-11-27)

3

Hi Marek,

Thanks a lot
Locate() works for DB2.

Need to get the first name from the string.

Change the same code to something like this

SUBSTR((DB2.SITE.SITE_MANAGER_FULLNAME_TYPE1),1,LOCATE(’ ',(DB2.SITE.SITE_MANAGER_FULLNAME_TYPE1))-1)
The code parses correctly but when run it in the report gives error
saying the second or third aguments in substring function is out of range.

Any work around for this issue.

BOWorker (BOB member since 2007-11-17)

4

This can help:

Marek Chladny :slovakia: (BOB member since 2003-11-27)

5

While I dont know the first thing about DB2, we do have an object in one of our Universes doing exactly the same thing on an Oracle database… I cant imagine that the syntax would be that different…

case when instr(AGENT_NAME,’, ‘) <>0 then substr(AGENT_NAME,(instr(AGENT_NAME,’,’)+2))||’ ‘||substr(AGENT_NAME,1,(instr(AGENT_NAME,’,’)-1))
when instr(AGENT_NAME,’,’) <>0 then substr(AGENT_NAME,(instr(AGENT_NAME,’,’)+1))||’ ‘||substr(AGENT_NAME,1,(instr(AGENT_NAME,’,’)-1))
else AGENT_NAME end

mrsnow (BOB member since 2006-11-16)

6

For Oracle below code is working fine.

SELECT SUBSTR('Smith,John', INSTR('Smith,John',',')+1 , LENGTH('Smith,John')) || ' '|| SUBSTR('Smith,John', 1, INSTR('Smith,John',',')-1)  AS NAME FROM DUAL

Rakesh_K :india: (BOB member since 2007-12-11)

7

The issue is with string in the field, Because its displays sometime
(Smith John) or (Smith , John) in the table and I need to change that to display John Smith in the report.
Tried to use a case function but that didn’t work.
Would able to split the string and get the second section with substr(). But not working to get the first section because the string separate by either by sapce or coma .
Have any idea how to create object in the universe to avoid this issue?.
Appreciate any help
Thanks!!

BO Worker.

BOWorker (BOB member since 2007-11-17)

8

Hi,

Try replacing a comma with a space first. Then the names will be separated by 1 or more spaces but there will be no comma. Then using the above logic find the first occurrence of a space in a string and make the split of the string from there. Maybe at the end, you will need to use also TRIM() function (or its equivalent in DB2) to get rid of the trailing spaces.

Marek Chladny :slovakia: (BOB member since 2003-11-27)