oracle 使用 Like 子句进行模式匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1499642/
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
Pattern Matching with Like Clause
提问by Thomas Aregger
I am attempting to use a LIKE clause in a SQL statement to match a certain pattern within Oracle.
我试图在 SQL 语句中使用 LIKE 子句来匹配 Oracle 中的某个模式。
I would like to do something as such:
我想做这样的事情：
LIKE '[A-Z][A-Z][1-4]%'
..but I can't use a regex because this is on Oracle9i(regex support came in 10g).
..但我不能使用正则表达式，因为这是在 Oracle9i 上（正则表达式支持来自 10g）。
I am attempting to match something that has two characters before it, then a number between 1 and 4 and that whatever beyond that. I have attempted this, but it doesn't seem to work. The only way I have been able to get it to work is by doing:
我试图匹配前面有两个字符的东西，然后是 1 到 4 之间的数字，以及超出此范围的数字。我已经尝试过这个，但它似乎不起作用。我能够让它工作的唯一方法是：
WHERE ...
LIKE '%1__' OR
LIKE '%2__' OR
LIKE '%3__' OR
LIKE '%4__'
I am not sure if the way I would like to do it is possible or the correct way as I have never attempted patterns with the LIKE clause.
我不确定我想要的方式是否可行或正确的方式，因为我从未尝试过使用 LIKE 子句的模式。
Any help you could give would be greatly appreciated.
您能提供的任何帮助将不胜感激。
回答by DCookie
Clunky, but perhaps:
笨重，但也许：
select *
from <your_table>
where TRANSLATE(SUBSTR(<blah>,1,3),'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234',
'AAAAAAAAAAAAAAAAAAAAAAAAAA1111') = 'AA1';
might suit your needs....
可能适合您的需求....
EDIT: Incorporated xlnt suggestion by @Hobo to translate the substring, rather than taking the substring of the translated string...
编辑：@Hobo 合并了 xlnt 建议来翻译子字符串，而不是采用翻译字符串的子字符串...
回答by Thomas Aregger
Try this:
尝试这个：
SELECT c1
FROM t1
WHERE substr(c1,1,1) IN ('A','B','C','D',
'E','F','G','H',
'I','J','K','L',
'M','N','O','P',
'Q','R','S','T',
'U','V','W','X',
'Y','Z')
AND substr(c1,2,1) IN ('A','B','C','D',
'E','F','G','H',
'I','J','K','L',
'M','N','O','P',
'Q','R','S','T',
'U','V','W','X',
'Y','Z')
AND substr(c1,3,1) IN ('1','2','3','4')
/
If you want also to match lower letters, then apply the upper() function to the 2 first substr(): eg. where upper(substr(c1,1,1)) in ...
如果您还想匹配小写字母，则将 upper() 函数应用于第 2 个 substr()：例如。其中 upper(substr(c1,1,1)) 在 ...
Performance
表现
I tested the Performance of the query with regexp_like and without. As you can see the query without the regexp_like function was 100% faster. (Note. Both querys did a soft-parse)
我使用 regexp_like 和不使用 regexp_like 测试了查询的性能。如您所见，没有 regexp_like 函数的查询速度提高了 100%。（注意。两个查询都进行了软解析）
SQL> select count(*) from t1;
COUNT(*)
----------
458752
Elapsed: 00:00:00.02
SQL> set timing off;
SQL> select count(*) from t1;
COUNT(*)
----------
458752
SQL> set timing on;
SQL> select count(*) from t1 where regexp_like(c1, '[A-Z][A-Z][1-4].*');
COUNT(*)
----------
65536
Elapsed: 00:00:02.66
SELECT count(*)
FROM t1
WHERE substr(c1,1,1) IN ('A','B','C','D',
'E','F','G','H',
'I','J','K','L',
'M','N','O','P',
'Q','R','S','T',
'U','V','W','X',
'Y','Z')
AND substr(c1,2,1) IN ('A','B','C','D',
'E','F','G','H',
'I','J','K','L',
'M','N','O','P',
'Q','R','S','T',
'U','V','W','X',
'Y','Z')
AND substr(c1,3,1) IN ('1','2','3','4')
18 /
COUNT(*)
----------
65536
Elapsed: 00:00:01.15
SQL>
Second Method
第二种方法
Get the ascii values of A,Z,1 and 4
获取 A、Z、1 和 4 的 ascii 值
SQL> select ascii('A') from dual;
ASCII('A')
----------
65
SQL> select ascii('Z') from dual;
ASCII('Z')
----------
90
SQL> select ascii('1') from dual;
ASCII('1')
----------
49
SQL> select ascii('4') from dual;
ASCII('4')
----------
52
Now you can write your statement a lot shorter
现在你可以把你的陈述写得更短
SELECT count(* )
FROM t1
WHERE ascii(substr(c1,1,1)) BETWEEN 65 AND 90
AND ascii(substr(c1,2,1)) BETWEEN 65 AND 90
AND ascii(substr(c1,3,1)) BETWEEN 49 AND 52
/
回答by OMG Ponies
I recommend using INSTR:
我建议使用INSTR：
INSTR(t.column, '1', 3, 1) > 0 OR
INSTR(t.column, '2', 3, 1) > 0 OR
INSTR(t.column, '3', 3, 1) > 0 OR
INSTR(t.column, '4', 3, 1) > 0
The problem with using a wildcard in a LIKE is there's no control over where in column value the 1/2/3/4/etc is going to turn up - it could be at the end.
在 LIKE 中使用通配符的问题是无法控制 1/2/3/4/etc 在列值中出现的位置 - 它可能在最后。
DCookie is right - this answer doesn'thave a way of handling if there's numeric data in that place. But it's still better than using LIKE with wildcards.
DCookie 是对的 -如果那个地方有数字数据，这个答案没有办法处理。但它仍然比使用带有通配符的 LIKE 更好。
回答by Ian Carpenter
Total wild card but will suggest it anyway.
完全通配符，但无论如何都会建议。
Does your 9i Database installation have the PL/SQL Web Toolkit installed? The reason for asking is that one of our customers pointed out that there is limited regular expression support using one of the supplied packages that comes with it.
您的 9i 数据库安装是否安装了 PL/SQL Web Toolkit？询问的原因是我们的一位客户指出，使用随附的提供的软件包之一提供的正则表达式支持有限。
The package is called owa_pattern and the only 9i link I could find is this one
该软件包被称为owa_pattern和唯一的9i的链接我能找到的是这样的一个
I have never used it and am still trying to get to grips with Regular Expressions so can't tell you if it would fit your purpose but thought that it may be of use.
我从未使用过它，并且仍在尝试掌握正则表达式，因此无法告诉您它是否适合您的目的，但认为它可能有用。
回答by tuinstoel
If you really want to use reg exps you can develop a java stored proc and a accompanying pl/sql wrapper. (I assume that the Java release supported in Oracle 9 supports reg exps, I am not 100% sure). You can call that java stored proc via the pl/sql wrapper in your select statement.
如果你真的想使用 reg exp，你可以开发一个 java 存储过程和一个随附的 pl/sql 包装器。（我假设 Oracle 9 中支持的 Java 版本支持 reg exp，我不是 100% 确定）。您可以通过 select 语句中的 pl/sql 包装器调用该 java 存储过程。
But more easy and faster:
但更简单、更快捷：
SELECT c1
FROM t1
WHERE substr(c1,1,1) between 'A' and 'Z'
AND substr(c1,2,1) between 'A' and 'Z'
AND substr(c1,3,1) IN ('1','2','3','4')
A variant of zürigschn?zlets 's solution without use of the ascci function.
不使用 ascci 函数的 zürigschn?zlets 解决方案的变体。
回答by CaffGeek
I think you want to use REGEXP_LIKE instead of like.
我认为您想使用 REGEXP_LIKE 而不是喜欢。
WHERE REGEXP_LIKE(fieldName, '[A-Z]{2}[1-4]?.+','i');
WHERE REGEXP_LIKE(fieldName, '[AZ]{2}[1-4]?.+','i');
