Use Oracle INSTR function to search for multiple strings

oracle, regex, sql, string

Solution

INSTR doesn't support regex ORs - you'd have to define INSTR function calls for each substring you want to check for. The equivalent of `regexp_instr('500 Oracle Parkway, Redwood Shores, CA','(Apple|Park|Shores)')` would be:

WHERE (INSTR('500 Oracle Parkway, Redwood Shores, CA', 'Apple') > 0
      OR
      INSTR('500 Oracle Parkway, Redwood Shores, CA', 'Park') > 0
      OR
      INSTR('500 Oracle Parkway, Redwood Shores, CA', 'Shores') > 0)

Depending on your needs, full text search functionality might be more towards what you want?

Problem

In Oracle/PLSQL, the `instr` function returns the location of a sub-string in a string. If the sub-string is not found, then `instr` will return `0`. I want to search multiple sub-strings in a string and return the first non-zero value. This can be achieved using `regexp_instr`, but I'd like a non-`regexp_` solution. Example: ``` regexp_instr('500 Oracle Parkway, Redwood Shores, CA','(Apple|Park|Shores)') ``` should return 12 (the location of 'Park').

Original source