Today i have received of the client requirement to separate the form One column and the data of clomun is like below. 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:
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 is used to start from Right side of ‘_’
- LENGTH is NOT going to append any trailing spaces in the end.
ReplyDeleteUsed the following to remove a comma from the address field, in this case there were commas between the address and unit number:
concat(SUBSTRING("Address" from 1 for Locate(',',"Address")-1),SUBSTRING("Address" from Locate(',',"Address")+1))