1.1從字符串中截取子字符串 SELECT regexp_substr('1PSN/231_3253/ABc', '[[:alnum:]]+') FROM dual; Output: 1PSN [[:alnum:]]+ 表示匹配1個(gè)或者多個(gè)字母或數(shù)字字符。 SELECT regexp_substr('1PSN/231_3253/ABc', '[[:alnum:]]+', 1, 2) FROM dual; Output: 231 與上面一個(gè)例子相比，多了兩個(gè)參數(shù) 1 表示從源字符串的第一個(gè)字符開始查找匹配 2 表示第2次匹配到的字符串（默認(rèn)值是“1”，如上例） select regexp_substr('@@/231_3253/ABc','@*[[:alnum:]]+') from dual; Output: 231 @* 表示匹配0個(gè)或者多個(gè)@ [[:alnum:]]+ 表示匹配1個(gè)或者多個(gè)字母或數(shù)字字符 注意：需要注意“+”和“*”的區(qū)別 select regexp_substr('1@/231_3253/ABc','@+[[:alnum:]]*') from dual; Output: @ @+ 表示匹配1個(gè)或者多個(gè)@ [[:alnum:]]* 表示匹配0個(gè)或者多個(gè)字母或數(shù)字字符 select regexp_substr('1@/231_3253/ABc','@+[[:alnum:]]+') from dual; Output: Null @+ 表示匹配1個(gè)或者多個(gè)@ [[:alnum:]]+ 表示匹配1個(gè)或者多個(gè)字母或數(shù)字字符 select regexp_substr('@1PSN/231_3253/ABc125','[[:digit:]]+$') from dual; Output: 125 [[:digit:]]+$ 表示匹配1個(gè)或者多個(gè)數(shù)字結(jié)尾的字符 select regexp_substr('@1PSN/231_3253/ABc','[^[:digit:]]+$') from dual; Output: /ABc [^[:digit:]]+$ 表示匹配1個(gè)或者多個(gè)不是數(shù)字結(jié)尾的字符 select regexp_substr('Tom_Kyte@oracle.com','[^@]+') from dual; Output: Tom_Kyte [^@]+ 表示匹配1個(gè)或者多個(gè)不是“@”的字符 select regexp_substr('1PSN/231_3253/ABc','[[:alnum:]]*',1,2) from dual; Output: Null [[:alnum:]]* 表示匹配0個(gè)或者多個(gè)字母或者數(shù)字字符 注：因?yàn)槭瞧ヅ?個(gè)或者多個(gè)，所以這里第2次匹配的是“/”（匹配了0次），而不是“231”，所以結(jié)果是“Null” 1.2匹配重復(fù)出現(xiàn) 查找連續(xù)2個(gè)小寫字母 SELECT regexp_substr('Republicc Of Africaa', '([a-z])/1', 1, 1, 'i')
FROM dual; Output: cc ([a-z]) 表示小寫字母a-z /1 表示匹配前面的字符的連續(xù)次數(shù) 1 表示從源字符串的第1個(gè)字符開始匹配 1 第1次出現(xiàn)符合匹配結(jié)果的字符 i 表示區(qū)分大小寫 1.3其他一些匹配樣式 查找網(wǎng)頁地址信息 SELECT regexp_substr('Go to http://www.oracle.com/products and click on database', 'http://([[:alnum:]]+/.?){3,4}/?') RESULT
insert into test values('112233445566778899'); insert into test values('22113344 5566778899'); insert into test values('33112244 5566778899'); insert into test values('44112233 5566 778899'); insert into test values('5511 2233 4466778899'); insert into test values('661122334455778899'); insert into test values('771122334455668899'); insert into test values('881122334455667799'); insert into test values('991122334455667788'); insert into test values('aabbccddee'); insert into test values('bbaaaccddee'); insert into test values('ccabbddee'); insert into test values('ddaabbccee'); insert into test values('eeaabbccdd'); insert into test values('ab123'); insert into test values('123xy'); insert into test values('007ab'); insert into test values('abcxy'); insert into test values('The final test is is is how to find duplicate words.');
commit;
一、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;
ID MC -------------------- ------------------------------------------------------------ A AAAAA a aaaaa
b bbbbb
SQL select * from test where regexp_like(id,'b','i'); --不區(qū)分?jǐn)?shù)據(jù)大小寫
ID MC -------------------- ------------------------------------------------------------
