How to convert INSTR function in OAC

Question

I have a requirement like , in table one of the column has the data like this 'SVDE:LOCATION1:123456'  , I need to get the data as output like first set of characters before this ':' this value.

' : ' this is not a constant position it was different for each record. so logic is like first to identify the ':' position and then written the substring from position 1

In DB we have written the query like below

select SUBSTR(column_name , 1, INSTR(column_name , ':') - 1) from table

Please let me know how to achieve this in OBIEE.I have used locate function instead of INSTR but it is giving the result as '0'.

your suggestions are appreciated for the solution in advance.

Federico Venturin - Oracle · Accepted Answer

Hi @User_BSR5K ,

You can use the following calculation to achieve your goal:

LEFT(ColumnName, LOCATE(':', ColumnName) - 1)

The LOCATE function returns the position of the first occurent of ':' character for each ColumnName value. The LEFT function returns a specified number of characters from the left of a string. Used in combination they allow you to get the substring before the first ':' character.