Use Oracle INSTR function to search for multiple strings

Question

In Oracle/PLSQL, the instr function returns the location of a substring in a string.

If the substring is not found, then instr will return 0.

I want to search multiple substrings in a string and return the first non-zero value. This can be acheived 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').

Answer 1

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?

Answer 2

Obviously without regexp there won't be such an elegant solution, unless you write your own PL/SQL function that does the same job. Otherwise you would have to do something like this:

with data as (select '500 Oracle Parkway, Redwood Shores, CA' string from dual)
select nvl(min(pos),0) from
( select instr(data.string, 'Apple') pos
  union all
  select instr(data.string, 'Park') pos
  union all
  select instr(data.string, 'Shores') pos
)
where pos > 0;