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) 

From 

(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:

SUBSTRING(LSP."LSP Name" FROM LOCATE('_',LSP."LSP Name")+1 FOR LENGTH(LSP."LSP Name"))

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:

 CASE  WHEN "inSightSMB".""."inSightSMB"."A6_IS_SMB_NODE_INTERFACE_DIM"."INTERFACE_TYPE" LIKE '%VLAN%' THEN  SUBSTRING("inSightSMB".""."inSightSMB"."A6_IS_SMB_NODE_INTERFACE_DIM"."INTERFACE_NAME_ALIAS" FROM  LOCATE('.', "inSightSMB".""."inSightSMB"."A6_IS_SMB_NODE_INTERFACE_DIM"."INTERFACE_NAME_ALIAS") -  LENGTH("inSightSMB".""."inSightSMB"."A6_IS_SMB_NODE_INTERFACE_DIM"."INTERFACE_NAME_ALIAS") FOR  LENGTH("inSightSMB".""."inSightSMB"."A6_IS_SMB_NODE_INTERFACE_DIM"."INTERFACE_NAME_ALIAS")) ELSE 'N/A' END

Thanks

Until next time