SQL 使用 Oracle INSTR 函数搜索多个字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2014414/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Use Oracle INSTR function to search for multiple strings
提问by Rudiger
In Oracle/PLSQL, the instrfunction returns the location of a sub-string in a string.
在 Oracle/PLSQL 中，该instr函数返回子字符串在字符串中的位置。
If the sub-string is not found, then instrwill return 0.
如果未找到子字符串，则instr返回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.
我想在一个字符串中搜索多个子字符串并返回第一个非零值。这可以使用 来实现regexp_instr，但我想要一个非regexp_解决方案。
Example:
例子：
regexp_instr('500 Oracle Parkway, Redwood Shores, CA','(Apple|Park|Shores)')
should return 12 (the location of 'Park').
应该返回 12（'Park' 的位置）。
采纳答案by OMG Ponies
INSTRdoesn'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:
INSTR不支持正则表达式 OR - 您必须为要检查的每个子字符串定义 INSTR 函数调用。相当于regexp_instr('500 Oracle Parkway, Redwood Shores, CA','(Apple|Park|Shores)')：
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?
根据您的需要，全文搜索功能可能更符合您的需求？
回答by Tony Andrews
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:
显然，如果没有正则表达式，就不会有如此优雅的解决方案，除非您编写自己的 PL/SQL 函数来完成相同的工作。否则你将不得不做这样的事情：
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;
回答by Uthaman
please check with below---
请检查以下---
select regexp_instr ('500 Oracle Parkway, Redwood Shores, CA', 'Apple|Park|Shores', 1, 1, 0, 'i') as result
from dual;
回答by Eddy
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
回答by Chanukya
according to me instrwill not give every position of the character in the string,so use the below code if you need.
根据我的说法，instr不会给出字符串中字符的每个位置，因此如果需要，请使用以下代码。
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.
在上面我正在检查字符串'HelloWorld'中的文字'l'，上面的查询给出了所有匹配的位置。您可以将上面的代码增强为功能并根据需要使用。
