oracle正则表达式去掉,Oracle 正则表达式跟trim的用法

Oracle 正则表达式和trim的用法

oracle去掉字符串结尾一些字符的问题

select rtrim(to_char(251.1245,'FM999999999990.999'),'.') from dual;

select REGEXP_REPLACE(to_char(251.123,'FM999999999990.999'),'\.$','') from dual;

Oracle正则表达式的使用(10g以上版本)以下摘自网络

一、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；

缓冲区从左到右编号, 通过\数字符号访问。 下面的例子列出了把名字 aa bb cc 变成cc, bb, aa.

Select REGEXP_REPLACE('aa bb cc','(.*) (.*) (.*)', '\3, \2, \1') FROM dual;