All of the topics discussed here in this blog comes from my real life encounters. They serve as references for future research. All of the data, contents and information presented in my entries have been altered and edited to protect the confidentiality and privacy of the clients.

Wednesday, July 25, 2012

Instring & Substring in OBIEE: How to trim data fields from or up to certain common characters

Hello there

Let's illustrate today's topic with a situation like below:

If you look at the data from the above column LSP name, you will find that all of the LSP names have an "_" in the middle somewhere. Well, except the one called "ingress-", which is an "-". However, the part before and after "_" may have different length. So now, if I want to display only the part before or after "_" regardless of the length or the location of "_", how would we do it?

Now let's start with thinking what we can do in the DB with a sql statement first. You see, what you would want to do initially is to find out the location of that character "_" in the text as a numeric value. Then based on the result, it will be used as the unit that you substring from. So let's look at the below SQL statement:

Select lsp_name, substr(lsp_name,1,f_s_l-1), substr(lsp_name,f_s_l+1) 
(Select lsp_name, instr(lsp_name,'_') f_s_l 
From is_smb_lsp_dim 

Now the "instr" function will pick up the location of "_", and the "substr" function will decide how many characters to keep. "f_s_l" is basically an alias of "instr(lsp_name,'_')".  So the result of this statement is satisfactory:

Now, let's see how we can translate this SQL in OBIEE. 

First of all, OBIEE doesn't have Instring function as a built in, the substring function also has a different syntax. However, the locate function in OBIEE does similar things like Instr. So we will start by creating a new column in the report that identifies the location of "_". We call it "Location".

The expression is "LOCATE('_',LSP."LSP Name")".

Now that this is working, we should move on to doing the substring. However, since the location of "_" is different from row to row, we will need to figure out what is the length of this column at each row first. So here, we use the "length" function and create another column called "length". Let's see what happen:

Now this is displaying the length of each row, so let's start with substring. In OBIEE, the subtring function takes the following syntax:

SUBSTRING(column name FROM integer FOR integer)

Therefore in our case, the column name is LSP name, and the "From" part has to be where the character "_" appears in each row. "For" should be the length of the characters it is subtringing.

After a few trials and errors, I have decided to enter the following code using length function in the "for" part
SUBSTRING(LSP."LSP Name" FROM LOCATE('_',LSP."LSP Name")-LENGTH(LSP."LSP Name") FOR LENGTH(LSP."LSP Name")). This will only return all of the characters before the "_". So I decided to test it out by creating another column and calling it "Left Of _" and it is working:

Now, if I want to display everything after "_", I will modify the code especially the "from" part to the following:


The "+1" in the "from" part will get rid of "_" itself, which is desired. Hence, changing the formula of "Left Of _" with the new code will produce the following result:

So now everything is working.

Of course, in my design I have eventually decided to derive this logic from the back end where the RPD is developed. The place to do it is in BMM layer and it is the same syntax:

And the actual code I am entering is following with a case condition:


Until next time


Antexity said...

Thanks, worked Great!

Anonymous said...

When I tried this - the trim only worked to the right of my character and not to the LEFT of - how can I get this to work and remove everything to the right of the characters and leave only the characters on the left?

Anonymous said...

To do what is required the formula should be

Leslie Lim said...

It is great to have the opportunity to read a good quality article with useful information on topics that plenty are interested on.


SGuha said...

Thanks for posting this article. I am currently working on a similar problem and after googling, found your article. My problem statement is a slight variant of yours. In my case, my string can have multiple underscores and I want the substrings before and after the last underscore. So, if the text is ABC, it should return 'ABC' & NULL. If it's ABC_12, it should return 'ABC' & '12' and if the text is ABC_12_pqr_97, it should return 'ABC_12_pqr' & 97. The solution that you described doesn't work in this scenario. LOCATE function only gives you the position of the 1st occurence of the substring but what I need is the position of the Nth occurence of that substring(which basically INSTR() function in SQL does). Is there any alternate way of deriving it?

Related Posts Plugin for WordPress, Blogger...