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').
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?
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;
please check with below---
select regexp_instr ('500 Oracle Parkway, Redwood Shores, CA', 'Apple|Park|Shores', 1, 1, 0, 'i') as result
from dual;
select
coalesce(
nullif(instr('500 Oracle Parkway, Redwood Shores, CA','Apple'),0),
nullif(instr('500 Oracle Parkway, Redwood Shores, CA','Park'),0),
nullif(instr('500 Oracle Parkway, Redwood Shores, CA','Shores'),0)
) as xxx
from dual
according to me instr will not give every position of the character in the string,so use the below code if you need.
SELECT literal,indx FROM (SELECT substr('HelloWorld',level,1) as literal,level as indx FROM DUAL CONNECT BY LEVEL <= length('HelloWorld'))WHERE literal IN ('l','o') ORDER BY literal
in the above i was checking the literal 'l' in the string 'HelloWorld',the above query gives all the matched positions.you can enhance the above code to a fucntion and use according to requirements.
