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

Substring and Locate

Received Response
53
Views
4
Comments
Joe Choueiri-Oracle
Joe Choueiri-Oracle Rank 5 - Community Champion
edited Aug 13, 2024 5:14PM in Oracle Analytics Cloud and Server

Hi

It is in OBIEE 12

I am using the substring and locate functions to find a specific word in a text field and it is fine but when that word I am looking for does not exist in the text, it is capturing the first word in the text...how can I have it so when that word I am looking for is not in the text then return blank?

Thanks

Joe

Answers

  • [Deleted User]
    [Deleted User] Rank 5 - Community Champion

    What's the function you're using now?

    You can always simply put a CASE WHEN... around the whole thing and check whether the result of your substr has a length of 0 for example.

  • Joe Choueiri-Oracle
    Joe Choueiri-Oracle Rank 5 - Community Champion

    I was going to try to use the substr and locate etc to find a specific word among a paragraph which I was able to do in OBIEE 12C but it seems it is not working for me in OAC DV..so I decided to write a case statement to say : case when "column that has a text" like '%##DIS%' THEN '##DIS' else '' end...will that work? it seems working, do you think I might face issues?

  • [Deleted User]
    [Deleted User] Rank 5 - Community Champion

    Yes that's exactly the way to write it.

  • Joe Choueiri-Oracle
    Joe Choueiri-Oracle Rank 5 - Community Champion

    Thank you Christian, really appreciate it.