TRANSLATE在ORACLE9里的使用

 

TRANSLATE在ORACLE9里的使用

2007-03-22 13:56

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