oracle Pl/Sql 使用 instr 查找完全匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12266013/
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
Pl/Sql using instr to find exact match
提问by devdar
I am trying to find if a string exist in a word and extract it. I have uses the instr()function but this works as the LIKE function: if part or the whole word exists it returns it.
我正在尝试查找单词中是否存在字符串并提取它。我已经使用了该instr()函数，但这就像 LIKE 函数一样：如果存在部分或整个单词，则返回它。
Here I want to get the string 'Services' out, it works but if I change 'Services' to 'Service' it still works. I don't want that. If 'Service' is entered it should return null and not 'Services'
在这里，我想取出字符串“Services”，它可以工作，但是如果我将“Services”更改为“Service”，它仍然可以工作。我不想要那个。如果输入“Service”，则应返回 null 而不是“Services”
Modified:
修改的：
What I am trying to do here is abbreviate certain parts of the company name.
我在这里试图做的是缩写公司名称的某些部分。
This is what my database table looks like :
这是我的数据库表的样子：
Word | Abb
---------+-----
Company | com
Limited | ltd
Service | serv
Services | servs
Here is the code:
这是代码：
Declare
Cursor Words Is
SELECT word,abb
FROM abbWords
processingWord VARCHAR2(50);
abbreviatedName VARCHAR(120);
fullName = 'A.D Company Services Limited';
BEGIN
FOR eachWord IN Words LOOP
--find the position of the word in name
wordPosition := INSTR(fullName, eachWord.word);
--extracts the word form the full name that matches the database
processingWord := Substr(fullName,instr(fullName,eachWord.word), length(eachWord.word));
--only process words that exist in name
if wordPosition > 0 then
abbreviatedName = replace(fullName, eachWord.word,eachWord.abb);
end if;
END lOOP;
END;
So if the user enters 'Service' I don't want 'Services' to be returned. By this I mean word position should be 0 if the word 'Service' in not found instead of returning the position for the word 'Services'
因此，如果用户输入“服务”，我不希望返回“服务”。我的意思是，如果找不到“服务”一词，而不是返回“服务”一词的位置，则词位置应为 0
回答by eis
One way of doing it:
一种方法：
DECODE(INSTR('A.D Company Seervices Limited','Services'),
0,
NULL,
SUBSTR('A.D Company Services Limited',
INSTR('A.D Company Services Limited','Services'),
length('Services')))
INSTR()will return 0 if text is not found. DECODE()will evaluate the first argument, compare to the second, if match, return third argument, if not, return fourth argument. (sqlfiddle link)
INSTR()如果未找到文本，将返回 0。DECODE()将评估第一个参数，与第二个参数进行比较，如果匹配，则返回第三个参数，如果不匹配，则返回第四个参数。( sqlfiddle 链接)
Arguably not the most elegant way, but matches your requirement.
可以说不是最优雅的方式，但符合您的要求。
回答by Ben
I think you're over-complicating this. You can do everything with regular expressions. For instance; given the following table:
我认为你把这个问题复杂化了。你可以用正则表达式做任何事情。例如; 给出下表：
create table names ( name varchar2(100));
insert into names values ('A.D Company Services Limited');
insert into names values ('A.D Company Service Limited');
This query will only return the name 'A.D Company Services Limited'.
此查询将仅返回 name 'A.D Company Services Limited'。
select *
from names
where regexp_like( name
, '(^|[[:space:]])services($|[[:space:]])'
, 'i' )
This means match the beginning of the string, ^, or a space followed by services followed the end of the string, $, or a space. This is what differentiates regular expressions from using instretc. You can make your matches easily conditional on other factors.
这意味着匹配字符串的开头^, 或空格后跟服务，然后是字符串的结尾$, 或空格。这就是正则表达式与使用instr等的区别。您可以根据其他因素轻松地进行匹配。
However, though this seems to be your question I don't think this is what you're trying to do. You're trying to replace the string 'serv'in your wider string without replacing 'services'or 'service'. For this you need to use regexp_replace().
然而，虽然这似乎是你的问题，但我认为这不是你想要做的。您正在尝试替换'serv'更宽字符串中的字符串而不替换'services'或'service'。为此，您需要使用regexp_replace().
If I add the following row to the table:
如果我将以下行添加到表中：
insert into names values ('A.D Company Serv Limited');
and run this query:
并运行此查询：
select regexp_replace( name
, '(^|[[:space:]])serv($|[[:space:]])'
, ' Services '
, 1, 0, 'i' )
from names
The onlything that will change is ' Serv ', which in this newest line, will be replaced with ' Services '. Note the spaces; as you don't want to replace 'Services'with 'ServServices'these are very important.
该唯一会改变的是' Serv '，在这个最新的线，将被替换' Services '。注意空格；因为你不想'Services'用'ServServices'这些替换是非常重要的。
Here's a little SQL Fiddleto demonstrate.
这里有一个小的 SQL Fiddle来演示。
回答by Banu Alexandru
Another alternative is to use something like:
另一种选择是使用类似的东西：
select replace(name,' serv ', ' Services ')
from names;
This will replace only the word 'Serv' situated between 2 spaces.
这将仅替换位于 2 个空格之间的单词“Serv”。
Thank you, Alex.
谢谢你，亚历克斯。
回答by Vincent Malgrat
INSTRreturns a number: the index of the first occurrence of the matching string. You should use regexp_substrinstead (10g+):
INSTR返回一个数字：匹配字符串第一次出现的索引。您应该regexp_substr改用 (10g+)：
SQL> select regexp_substr('A.D Company Services Limited', 'Services') match,
2 regexp_substr('A.D Company Service Limited', 'Services') unmatch
3 from dual;
MATCH UNMATCH
-------- -------
Services
