STRING FUNCTION IN OBIEE

Hi All,

String functions are very easy to use in Oracle. However, some of them (String functions) in particular have little different story sometimes in OBIEE.

Let’s discuss one such case here today:

Have a look at below column. Let’s say we want to separate text which is appearing before ‘_’ (underscore) and the one which is appearing after.

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?

Unlike SQL, we cant use INSTR in OBIEE. However, we can use LOCATE function to achieve the same.

Enter this text in New Column Formula:

Locate(‘_’,”PresentationFolderName”.”ColumnName”)

The above tells the location of ‘_’. However, we also need to know the length of string in each case.

Enter this text in New Column Formula:

LENGTH(”PresentationFolderName”.”ColumnName”)

Now, we are almost towards the end. Finally use the substring command with below syntax:

SUBSTRING(column name FROM integer FOR integer)

a)       Thus, for string before (on left) of ‘_’, we will extract characters from left till the location of ‘_’ comes. And thus, the formula:

SUBSTRING(“PresentationFolderName “.”ColumnName” from 1 for Locate(‘_’,” PresentationFolderName “.” ColumnName “)-1)

We are using -1 at the end because we want to exclude ‘_’ from our output.

b)       Now for the right part of the string, use below formula

SUBSTRING(“PresentationFolderName “.”ColumnName” from Locate(‘_’,” PresentationFolderName “.” ColumnName “)+1 for LENGTH(”PresentationFolderName”.”ColumnName”))

Here:

1. +1 is used to start from Right side of ‘_’
2. LENGTH is NOT going to append any trailing spaces in the end.

Happy Exploring!