Using Substring

User_DAALX

Hi There,

I'm trying to use substring function to get the middle characters from a field as outlined below:

I have managed to get the surname separated as I need this as well by using the syntax below, but I am struggling with getting the middle characters.

SUBSTRING("Supervisor"."Supervisor Name" from 1 for Locate(',', "Supervisor"."Supervisor Name")-1)

Thanks

Gianni Ceresa

Look at the function LOCATEN , it allows to use a 3rd parameter being the character where to start looking for your search thing.

You will need to combine LOCATE and LOCATEN a few times to find, for example, the location of the space character before the name and then the space after it or just the end of the whole string, and then back to SUBSTRING using these 2 locations for the substring function.

User_DAALX

Thanks.

I have been able to locate the 1st space within the string, but I'm stuck on how to locate the 2nd space. I have outlined below the syntax used to get the 1st space:

1st Space (No Error)

substring("Supervisor"."Supervisor Name",(locate(' ',"Supervisor"."Supervisor Name") + 1),(length("Supervisor"."Supervisor Name") - length(SUBSTRING("Supervisor"."Supervisor Name" from 1 for Locate(',', "Supervisor"."Supervisor Name")-1))))

2ns space (Errors)

substring("Supervisor"."Supervisor Name",(locate(' ',"Supervisor"."Supervisor Name") + 1),locate(' ',"Supervisor"."Supervisor Name",(LOCATE(' ',"Supervisor"."Supervisor Name") + 1),(length("Supervisor"."Supervisor Name") - length(SUBSTRING("Supervisor"."Supervisor Name" from 1 for Locate(',', "Supervisor"."Supervisor Name")-1)))))

User_DAALX

Hi Gianni,

I'm still struggling to get this to work and I was wondering if you could help me with the syntax, which runs fine but still isn't locating the first name from the string as outlined below:

Using the syntax below:

SUBSTRING("Supervisor"."Supervisor Name" FROM LOCATE(' ',"Supervisor"."Supervisor Name", LOCATE(' ',"Supervisor"."Supervisor Name")+1) FOR (LOCATE(' ',"Supervisor"."Supervisor Name", LOCATE(' ',"Supervisor"."Supervisor Name",LOCATE(' ',"Supervisor"."Supervisor Name")+1)-LOCATE(' ',"Supervisor"."Supervisor Name")-1)))

Thanks,

Lola

Gianni Ceresa

You just have to take it step by step and combine all the pieces like when playing with Lego...

TRIM(BOTH ' ' FROM 
  SUBSTRING('De Army, Miss Lucinda Diana' 
    FROM LOCATE(' ', 'De Army, Miss Lucinda Diana', LOCATE(', ', 'De Army, Miss Lucinda Diana')+2) 
    FOR LOCATE(' ', 'De Army, Miss Lucinda Diana', LOCATE(' ', 'De Army, Miss Lucinda Diana', LOCATE(', ', 'De Army, Miss Lucinda Diana')+2)+1) 
        - LOCATE(' ', 'De Army, Miss Lucinda Diana', LOCATE(', ', 'De Army, Miss Lucinda Diana')+2)
  )
)

User_DAALX

Thank you, I'm sort of getting there using your example which I found very useful. This has been turned into:

SUBSTRING("Supervisor"."Supervisor Name" FROM LOCATE(' ', "Supervisor"."Supervisor Name", LOCATE(', ', "Supervisor"."Supervisor Name")+2) FOR LOCATE(' ', "Supervisor"."Supervisor Name", LOCATE(' ', "Supervisor"."Supervisor Name", LOCATE(', ', "Supervisor"."Supervisor Name")+2)+1) - LOCATE(' ', "Supervisor"."Supervisor Name", LOCATE(', ', "Supervisor"."Supervisor Name")+2))

The above works for all names like De Army, Lucinda Diana, but leaves a blank for those with single first names like De Army, Lucinda

I really do appreciate your help as all what I've been learning on Obiee have been from communities like these as I learn by examples.

Many thanks,

Lola

Gianni Ceresa

I was expecting that kind of reply, but in all fairness you only gave examples with a fixed format in the first place...

You are taking the problem in the wrong way...

Did you look once at the query sent to the database with that kind of formula? It will perform poorly and you are trying to have OBIEE doing the job that should be done somewhere else.

Why isn't your data cleansed and prepared in the ETL steps before to be used for analytics? OBIEE isn't a database or an ETL or anything like that, data preparation isn't supposed to be a job for OBIEE but something you do before.

[Deleted User]

+1 to Gianni and I'm surprised it took so long to get to this point:

The source data is rubbish. Analytics is there to analyze data, make sense of it and make it valuable. It's not there to massage tons of data into a usable for just because the source system delivers poor content.

"Logic" like the above (and I'm using the term logic lightly) may make sense in some situations inside of transactional systems which work record by record. Analytical systems work with massive numbers of records at a time. Implementing what's basically data cleansing live, on-the-fly on multi-million or billion records queries is non-sensical in analytics.

User_DAALX

Thanks both for your comments, it's sad but true that we only get to manipulate the data as you've suspected at a higher level (analytics) and when we get little to no help when we need to find workarounds to poor data setup in the system which is outsourced to a third party. Some of the data items in the system haven't been done in the best interest of the users which makes it hard to manipulate data and I'm also fairly new to Obiee as well.

Thanks,

Lola