保存在這里，方便自己以后查看 (⊙_⊙) ╮(╯▽╰)╭
這個(gè)是函數(shù)中將會(huì)用到的模式串的介紹：
Oracle 中的正則函數(shù)一共有5個(gè)：
REGEXP_COUNT -- 統(tǒng)計(jì)子串出現(xiàn)的次數(shù)
REGEXP_INSTR -- 查找子串在母串中的位置
REGEXP_LIKE -- 模糊查詢子串
以下是官方關(guān)于這5個(gè)函數(shù)可能會(huì)用到的參數(shù)的介紹：
· source_char is a character expression that serves as the search value. It is commonly a character column and can be of any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
· pattern is the regular expression. It is usually a text literal and can be of any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. It can contain up to 512 bytes. If the datatype of pattern is different from the datatype of source_char, Oracle Database converts pattern to the datatype of source_char. For a listing of the operators you can specify in pattern, please refer to Appendix C, "Oracle Regular Expression Support".
· position is a positive integer indicating the character of source_char where Oracle should begin the search. The default is 1, meaning that Oracle begins the search at the first character of source_char.
· occurrence is a positive integer indicating which occurrence of pattern in source_char Oracle should search for. The default is 1, meaning that Oracle searches for the first occurrence of pattern.
· return_option lets you specify what Oracle should return in relation to the occurrence:
o If you specify 0, then Oracle returns the position of the first character of the occurrence. This is the default.
o If you specify 1, then Oracle returns the position of the character following the occurrence.
· match_parameter is a text literal that lets you change the default matching behavior of the function. You can specify one or more of the following values for match_parameter:
o 'i' specifies case-insensitive matching.
o 'c' specifies case-sensitive matching.
o 'n' allows the period (.), which is the match-any-character character, to match the newline character. If you omit this parameter, the period does not match the newline character.
o 'm' treats the source string as multiple lines. Oracle interprets ^ and $ as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. If you omit this parameter, Oracle treats the source string as a single line.
o 'x' ignores whitespace characters. By default, whitespace characters match themselves.
1. REGEXP_COUNT(<source_string>, <pattern>[[, <start_position>], [<match_parameter>]])
-- 從'123123123123123'串中查找'123'子串的個(gè)數(shù)，起始查找位置為1select regexp_count('123123123123123', '123', 1, 'i') from dual;
→ 5
2. REGEXP_INSTR(<source_string>, <pattern>
[[, <start_position>][, <occurrence>][, <return_option>][, <match_parameter>][,<sub_expression>]])
-- 從'500 Oracle Parkway, Redwood Shores, CA'串中查找 至少包含一個(gè)非空格字符的子串的位置-- 起始查找位置為1，查找匹配的第6個(gè)子串的位置select regexp_instr('500 Oracle Parkway, Redwood Shores, CA', '[^ ]+', 1, 6) from dual;→ 37-- 從'500 Oracle Parkway, Redwood Shores, CA'串中查找-- 包含s或r或p字符，后面跟著6個(gè)字母的子串，不區(qū)分大小寫('Parkway','Redwood'匹配)-- 起始查找位置為3，查找匹配的第2個(gè)子串('Redwood'匹配)-- 返回的位置是緊接著匹配子串后面的字符的位置('Redwood'后面空格的位置)select regexp_instr('500 Oracle Parkway, Redwood Shores, CA', '[s|r|p][[:alpha:]]{6}', 3, 2, 1, 'i') from dual;→ 28
3. REGEXP_LIKE(<source_string>, <pattern>, <match_parameter>)
-- 從指定列中查詢出含有2個(gè)連續(xù)空格的字段select source_string from (select 'hello, one space!' as source_string from dual union all select 'hello, two space! ' as source_string from dual union all select 'hello, two sequential space! ' as source_string from dual) where regexp_like(source_string, '[[:space:]]{2}'); -- 從指定列中查詢出first_name列以'Ste'開(kāi)頭，'en'結(jié)尾，中間包含字母'v'或'ph'的字段select first_name, last_name from (select 'Steven' as first_name, 'King' as last_name from dual union all select 'Steven' as first_name, 'Markle' as last_name from dual union all select 'Stephen' as first_name, 'Stiles' as last_name from dual) where regexp_like(first_name, '^Ste(v|ph)en$') order by first_name, last_name;
4. REGEXP_REPLACE(<source_string>, <pattern>, <replace_string>
[, <position>[, <occurrence>[, <match_parameter>]]])
-- 將字符串中連續(xù)的多個(gè)空格替換成一個(gè)空格select regexp_replace('500 Oracle Parkway, Redwood Shores, CA', '( ){2,}', ' ') from dual; -- 把'xxx.xxx.xxxx'格式的電話號(hào)碼轉(zhuǎn)換為'(xxx) xxx-xxxx'格式with T1 as( select '515.123.4567' as phone_number from dual union all select '515.123.4568' as phone_number from dual union all select '(515) 123 4569' as phone_number from dual union all select '13207730591' as phone_number from dual)select regexp_replace(phone_number, '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})', '(\1) \2-\3') from T1; -- 給字段的每個(gè)字符后面加上空格with T2 as( select 'Argentina' as country_name from dual union all select 'Australia' as country_name from dual union all select 'Belgium' as country_name from dual union all select 'Brazil' as country_name from dual union all select 'Canada' as country_name from dual)select regexp_replace(country_name, '(.)', '\1 ') from T2;
5. REGEXP_SUBSTR(<source_string>, <pattern>[, <position> [, <occurrence>[, <match_parameter>]]])
-- 截取字符串中兩個(gè)','之間的子串，子串只能開(kāi)頭和結(jié)尾含有','，中間至少要含有一個(gè)非','字符 select regexp_substr('500 Oracle Parkway, Redwood Shores, CA', ',[^,]+,') from dual; -- 截取字符串中包含 'http://' 子串，后面跟著3-4個(gè) '字母/數(shù)字 0-1個(gè).'，再跟著0-1個(gè) '/' 的子串 select regexp_substr('http://www.oracle.com/products', 'http://([[:alnum:]]+\.?){3,4}/?') from dual; with T3 as(select '1:3,4:6,8:10,3:4,7:6,11:12' as source_string from dual) -- 從source_string列中查找不含':'的子串，起始查找位置為1，返回第1個(gè)子串 select regexp_substr(source_string,'[^:]+', 1, 1) from T3 union all -- 從source_string列中查找不含':'的子串，起始查找位置為3，返回第2個(gè)子串 select regexp_substr(source_string,'[^:]+', 3, 2) from T3;
聯(lián)系客服
