Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

How to convert INSTR function in OAC

Accepted answer
134
Views
2
Comments
User_BSR5K
User_BSR5K Rank 1 - Community Starter
edited Aug 13, 2024 7:20PM in Oracle Analytics Cloud and Server

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.

Best Answer

  • Federico Venturin - Oracle
    Federico Venturin - Oracle Mod
    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.

Answers