to be continue...
在上次的项目里面 我们遇到了一个小问题
由于没有严格控制 客户在一些表格里面输入了 非法字符 导致我们的数据库查询出现错误
BAIDU后发现一个很有用的OEACLE正则表达函数 BUT这个东西要在ORACLE10才可以很方便的使用
一、REGEXP_LIKE
select * from test where regexp_like(mc,'^a{1,3}');
select * from test where regexp_like(mc,'a{1,3}');
select * from test where regexp_like(mc,'^a.*e$');
select * from test where regexp_like(mc,'^[[:lower:]]|[[:digit:]]');
select * from test where regexp_like(mc,'^[[:lower:]]');
Select mc FROM test Where REGEXP_LIKE(mc,'[^[:digit:]]');
Select mc FROM test Where REGEXP_LIKE(mc,'^[^[:digit:]]');
二、REGEXP_INSTR
Select REGEXP_INSTR(mc,'[[:digit:]]$') from test;
Select REGEXP_INSTR(mc,'[[:digit:]]+$') from test;
Select REGEXP_INSTR('The price is $400.','$[[:digit:]]+') FROM DUAL;
Select REGEXP_INSTR('onetwothree','[^[[:lower:]]]') FROM DUAL;
Select REGEXP_INSTR(',,,,,','[^,]*') FROM DUAL;
Select REGEXP_INSTR(',,,,,','[^,]') FROM DUAL;
三、REGEXP_SUBSTR
SELECT REGEXP_SUBSTR(mc,'[a-z]+') FROM test;
SELECT REGEXP_SUBSTR(mc,'[0-9]+') FROM test;
SELECT REGEXP_SUBSTR('aababcde','^a.*b') FROM DUAL;
四、REGEXP_REPLACE
Select REGEXP_REPLACE('Joe Smith','( ){2,}', ',') AS RX_REPLACE FROM dual;
Select REGEXP_REPLACE('aa bb cc','(.*) (.*) (.*)', '3, 2, 1') FROM dual；
SQL> select * from test;
没办法 、只好用老的方法了translate
Purpose
TRANSLATE returns char with all occurrences of each character in from_string
replaced by its corresponding character in to_string. Characters in char that are
TO_YMINTERVAL ( char )
TRANSLATE ( ’ char ’ , ’ from_string ’ , ’ to_string ’ )
TRANSLATE
---------------------------------------------------
如何找出一个字符型字段中包含非数字的那些记录？
SQL> select ltrim('asadad434323', '0123456789') from dual;
not in from_string are not replaced. The argument from_string can contain
more characters than to_string. In this case, the extra characters at the end of
from_string have no corresponding characters in to_string. If these extra
characters appear in char, then they are removed from the return value.
You cannot use an empty string for to_string to remove all characters in from_
string from the return value. Oracle interprets the empty string as null, and if this
function has a null argument, then it returns null.
Examples
The following statement translates a license number. All letters ’ABC...Z’ are
translated to ’X’ and all digits ’012 . . . 9’ are translated to ’9’:
SELECT TRANSLATE(’2KRW229’,
’0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ’,
’9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX’) "License"
FROM DUAL;
License
--------
9XXX999
