Oracle Analytics Forum

Home› Oracle Analytics and AI› Oracle Analytics and AI Forums› Oracle Analytics Forums› Oracle Analytics Forum

Categories

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

Using LOCATE() with Wildcards

Accepted answer
19
Views
3
Comments
Dave Brandon
Dave Brandon Rank 1 - Community Starter
in Oracle Analytics Forum

Is it possible to use LOCATE(), or a similar function, with wildcards?

For example, if i'm searching in a string for '%N.___.3__%'. Is it possible to use an existing function to find and extract the value using SUBSTRING()?

Tagged:

Best Answer

  • Gianni Ceresa
    Gianni Ceresa Mod
    edited 1:26PM Answer ✓

    LOCATE doesn't support wildcards like the LIKE operator does in SQL with '_' and '%'.

    And I don't remember any regular expression string function in LSQL. Depending what you are trying to do, if a regex is what you need, you will need to consider pushing that down to your database if that's possible.

Answers

  • Brendan T
    Brendan T Rank 7 - Analytics & AI Coach

    the example in this link might help

    https://forums.oracle.com/ords/apexds/post/how-to-use-locate-function-if-multiple-occurences-of-same-c-7128

  • Gianni Ceresa
    Gianni Ceresa Mod
    edited 4:55PM

    Adding as extension to the lack of wildcards or regexp on LSQL, you can translate a '%N.__.3__%' into a series if LOCATE, SUBSTRING, If-Else conditions. It will be ugly, difficult to maintain and even more to explain what it does over time. And you better not have such kind of expression applied to more than a few rows, because you will pay a huge performance tax. That's why a quickwin could be EVALUATE to push to the database the job (it has more functions that can do the job better than LSQL), or ideally directly in the database as an integration step before to report and analyse data.