SQL正则表达式疑虑，求牛人解惑！

wzz0001

test表mc字段值如下：112233445566778899
22113344 5566778899
33112244 5566778899
44112233 5566 778899
5511 2233 4466778899
661122334455778899
771122334455668899
881122334455667799
991122334455667788
aabbccddee
bbaaaccddee
ccabbddee
ddaabbccee
eeaabbccdd
ab123
123xy
007ab
abcxy
The final test is is is how to find duplicate words.

列出所有包含字母的mc字段，可以这样写
SQL> Select mc FROM test Where REGEXP_LIKE(mc,'[[:alpha:]]');
MC
------------------------------------------------------------
aabbccddee
bbaaaccddee
ccabbddee
ddaabbccee
eeaabbccdd
ab123
123xy
007ab
abcxy
The final test is is is how to find duplicate words.


列出所有包含数字的mc字段，可以这样写
SQL> Select mc FROM test Where REGEXP_LIKE(mc,'[[:digit:]]');
MC
------------------------------------------------------------
112233445566778899
22113344 5566778899
33112244 5566778899
44112233 5566 778899
5511 2233 4466778899
661122334455778899
771122334455668899
881122334455667799
991122334455667788
ab123
123xy
007ab
列出所有不包含纯数字的mc字段，可以这样写
SQL> select mc from test where regexp_like(mc,'[^[:digit:]]');

MC
------------------------------------------------------------
22113344 5566778899
33112244 5566778899
44112233 5566 778899
5511 2233 4466778899
aabbccddee
bbaaaccddee
ccabbddee
ddaabbccee
eeaabbccdd
ab123
123xy
007ab
abcxy
The final test is is is how to find duplicate words.
列出所有不包含纯字母的mc字段，可以这样写
SQL> select mc from test where regexp_like(mc,'[^[:alpha:]]');

MC
------------------------------------------------------------
112233445566778899
22113344 5566778899
33112244 5566778899
44112233 5566 778899
5511 2233 4466778899
661122334455778899
771122334455668899
881122334455667799
991122334455667788
ab123
123xy
007ab
The final test is is is how to find duplicate words.

如果要得到纯数字的mc字段，应该怎么写？
如果要得到纯字母的mc字段，应该怎么写？

wzz0001

高人在哪里？

li0924

1.先不用正则

1. --得到纯数字的mc字段
   
2. select mc from test where ltrim(mc,'0123456789') is not null;
   
3. --得到纯字母的mc字段
   
4. --同上；但不推荐。

复制代码

2使用正则

1. --得到纯数字的mc字段
   
2. select mc from test where regexp_replace(mc, '[[:digit:]]', '')  is null;
   
3. --得到纯字母的mc字段
   
4. select mc from test where regexp_replace(mc, '[[:alpha:]]', '')  is null;

复制代码

jackson198574

回复 3# li0924


    赞！~~~

jackson198574

回复 5# landingof


  

li0924

回复 6# jackson198574
@landingof 你们太客气了！

1. --等到纯数字mc字段
   
2. select mc from test where regexp_instr(mc,'[^[:digit:]]') = 0;
   
3. --等到纯字符mc字段
   
4. select mc from test where regexp_instr(mc,'[^[:alpha:]]') = 0;

复制代码