刚下了几篇正则表达式的文章.做第一个实验就有点问题.
下面是文章中的例子
create table tmp as
( select 'like' as id ,'a9999' as str from dual union all
select 'like' ,'a9c' from dual union all
select 'like' ,'A7007' from dual union all
select 'like' ,'123a34cc' from dual union all
select 'substr' ,'123,234,345' from dual union all
select 'substr' ,'12,34.56:78' from dual union all
select 'substr' ,'123456789' from dual union all
select 'instr' ,'192.168.0.1' from dual union all
select 'replace' ,'(020)12345678' from dual union all
select 'replace' ,'001517729C28' from dual);
select str from tmp where id='like' and regexp_like(str,'A\d+','i'); -- 'i' 忽略大小写STR-------------a9999a9cA7007123a34cc这是我执行的情况.SQL> select * from tmp;
ID STR
------- -------------
like a9999
like a9c
like A7007
like 123a34cc
substr 123,234,345
substr 12,34.56:78
substr 123456789
instr 192.168.0.1
replace (020)12345678
replace 001517729C28
10 rows selected
SQL>
SQL> select str from tmp where id='like' and regexp_like(str,'A\d+','i');
STR
-------------
SQL>
下面是文章中的例子
create table tmp as
( select 'like' as id ,'a9999' as str from dual union all
select 'like' ,'a9c' from dual union all
select 'like' ,'A7007' from dual union all
select 'like' ,'123a34cc' from dual union all
select 'substr' ,'123,234,345' from dual union all
select 'substr' ,'12,34.56:78' from dual union all
select 'substr' ,'123456789' from dual union all
select 'instr' ,'192.168.0.1' from dual union all
select 'replace' ,'(020)12345678' from dual union all
select 'replace' ,'001517729C28' from dual);
select str from tmp where id='like' and regexp_like(str,'A\d+','i'); -- 'i' 忽略大小写STR-------------a9999a9cA7007123a34cc这是我执行的情况.SQL> select * from tmp;
ID STR
------- -------------
like a9999
like a9c
like A7007
like 123a34cc
substr 123,234,345
substr 12,34.56:78
substr 123456789
instr 192.168.0.1
replace (020)12345678
replace 001517729C28
10 rows selected
SQL>
SQL> select str from tmp where id='like' and regexp_like(str,'A\d+','i');
STR
-------------
SQL>
以a或A开头的倒是没错.
SQL> select str from tmp where id='like' and regexp_like(str,'^A','i');
STR
-------------
a9999
a9c
A7007
SQL>
难道是版本的问题?
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as scott
-----没问题啊
SQL> select * from tmp;ID STR
------- -------------
like a9999
like a9c
like A7007
like 123a34cc
substr 123,234,345
substr 12,34.56:78
substr 123456789
instr 192.168.0.1
replace (020)12345678
replace 001517729C2810 rows selectedSQL> select str from tmp where id='like' and regexp_like(str,'A\d+','i');STR
-------------
a9999
a9c
A7007
123a34cc
--原因:反斜线在正则表达式中有另一种意义,取决于上下文,它还可能表示 Escape 字符。
--你查询不出来,原因可能就是你反斜线有特殊含义。
我记得好像可以用change替换字符串命令来改变 具体怎么做我忘了,你可以查下。
好像是SQLPLUS命令
我觉得就是我说的那个原因,反斜线可能是转义字符escape了!
你执行show escape 看看是不是“\”
如果是,你先关闭(set define off)之后再执行你的sql语句试试
用change命令改变转义字符试试
SQL> select str from tmp where id='like' and regexp_like(str,'A\d+','i'); STR
-------------
a9999
a9c
A7007
123a34cc
select str from tmp where id='like' and regexp_like(str,'A+','i');
SQL> select str from tmp where id='like' and regexp_like(str,'A\d+','i');
STR
-------------
SQL>
select str from tmp where id='like' and regexp_like(str,'A[0-9]+','i');
行不?
SQL> select str from tmp where id='like' and regexp_like(str,'A[0-9]+','i');
STR
-------------
a9999
a9c
A7007
123a34cc
SQL>
这个肯定是可以的,我用select str from test where id='like' and regexp_like(str,'A[0-9]+','i');
也能查询出来,只是minitoy用反斜线就不行,我这里也不行,我也纳闷呢。我转换了escape也不行
SQL> conn livebos/livebos
已连接。
SQL> select str from tmp where id='like' and regexp_like(str,'A\d+','i');STR
-------------
a9999
a9c
A7007
123a34ccSQL> show escape
escape OFF
SQL> set escape on;
SQL> select str from tmp where id='like' and regexp_like(str,'A\d+','i');未选定行SQL> show escape;
escape "\" (hex 5c)
SQL>
SQL> set define off
SQL> select str from tmp where id='like' and regexp_like(str,'A\d+','i');
STR
-------------
SQL> select str from tmp where id='like' and regexp_like(str,'A[0-9]+','i');
STR
-------------
a9999
a9c
A7007
123a34cc
SQL> set escape on
SQL> select str from tmp where id='like' and regexp_like(str,'A\d+','i');
STR
-------------
SQL>
no !
SQL*Plus: Release 8.0.6.0.0 - Production on 星期一 11月 8 10:39:42 2010(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> select * from test;ID STR
-------------- --------------------------
like a9999
like a9c
like A7007
like 123a34cc
substr 123,234,345
substr 12,34.56:78
substr 123456789
instr 192.168.0.1
replace (020)12345678
replace 001517729C2810 rows selected.SQL> show escape
escape OFF
SQL> select str from test where id='like' and regexp_like(str,'A\d+','i');no rows selectedSQL> set escape on
SQL> show escape
escape "\" (hex 5c)
SQL> select str from test where id='like' and regexp_like(str,'A\d+','i');no rows selectedSQL>
http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/ap_posix.htm#g693775