(Les19 Regular expression support) [20180106], les1920180106

(Les19 Regular expression support) [20180106], les1920180106
Learning objective-regular meta-string-use regular search in SQL statements-use regular matching in SQL statements-use regular expressions in SQL statements to replace string regular expressions. Oracle Database 10 Gb introduces support for regular expressions. Compliant with the POSIX standard for UNIX portable operating systems, controlled by the Institute of Electrical and Electronics Engineers (IEEE), and the semantics and syntax for ASCII data matching. Regular Expressions are a simple and complex method for describing search and operation modes. Regular (POSIX) metacharacters '*' match the previous expression once or multiple times '|' indicates that one of the two items matches the start position of the input string. Note, if ^ is used in the square brackets expression, it indicates that this character set is not accepted. '[]' Marking a bracket expression '{m}' indicates the number of occurrences '{m, n} 'indicates the range of occurrences m <= occurrences <= n, '\' backslash itself, \ num-matched references, \ n escape characters, and nothing. '+' Match the previous subexpression once or multiple times '? 'Match the previous subexpression zero or once '. 'match any single character except linefeed '()' to mark the start and end position of a subexpression '[:]' indicates a character cluster :[[: alpha:] Any letter [[: digit:] Any number [[: alnum:] Any letter or number [[: space:] blank character [[: upper:] uppercase letters [[: lowe:] lowercase letters [[: punct:] Any punctuation [[: xdigit:] Any hexadecimal number, equivalent to [0-9a-fA-F] Regular Expression Function REGEXP_LIKE and LIKE functions are similar, matching string REGEXP_REPLACE is similar to REPLACE function, replacing string REGEXP_INSTR function is similar to INSTR function, marking character subscript REGEXP_SUBSTR function is similar to SUBTR function, returns the string REGEXP_COUNT at the specified position, which is similar to COUNT. Repeated occurrences of styles. They are used in the same way as Oracle SQL functions LIKE, INSTR, SUBSTR, and REPLACE, but they use POSIX regular expressions to REPLACE the old percent sign (%) and the wildcard. REGEXP_LIKE (srcstr, pattern [, match_option]) REGEXP_INSTR (srcstr, pattern [, position [, condition [, return_option [, match_option]) REGEXP_SUBSTR (srcstr, pattern [, position [, occurrence [, match_option]) REGEXP_REPLACE (srcstr, pattern [, replacestr [, position [, occurrence [, match_option])

Example 1: last_name: select * from employeeswhere regexp_like (last_name, '(G. g) {1} '); LAST_NAME EMPLOYEE_ID MANAGER_ID ------------------------- ---------- Gaga 203 Example 2 insert into employees values ('regexp02 _ test', 200 ); select * from employeeswhere regexp_instr (last_name, '[: digit:]')> 1; LAST_NAME EMPLOYEE_ID MANAGER_ID comment ----------- -------- limit 208 200 Example 3: extract the string col last_name format a20select regexp_substr (last_name, '[[: alpha:] *') last_name, employee_id, manager_idfrom employees; LAST_NAME EMPLOYEE_ID MANAGER_ID ------------------------- ---------- Dereck 100 1 Gary 200 100 300 Klec 100 203 Gaga 200 204 Wendy 205 200 206 Tinan 200 207 Ling 200 301 Owen 302 300Yi 300 208 Regexp 20011 rows selected. example 4: replace the value in last_name with the value ** col last_name format a20select regexp_replace (last_name, '[[: digit:]', '*') last_name, employee_id, manager_idfrom employees; LAST_NAME EMPLOYEE_ID MANAGER_ID ------------------------- ---------- Dereck 100 1 Gary 200 100 300 Klec 100 Gaga 203 200 204 Wendy 205 200Xi 200 206 Tinan 200 207 Ling 200 301 Owen 302 300Yi 208 300Regexp ** _ test 200 11 rows selected. example 5 calculate the number of times the value of last_name appears col last_name format a20select last_name, regexp_count (last_name, '[[: digit:]') t_count, employee_id, manager_idfrom employees; LAST_NAME T_COUNT EMPLOYEE_ID MANAGER_ID -------------------- ---------- ----------- ---------- Dereck 0 100 1 Gary 0 200 100 Klec 0 300 100 Gaga 0 203 200 Wendy 0 204 200Xi 0 205 Tinan 0 200 206 Ling 0 200 207 Owen 0 301 300Yi 0 302 300Regexp02_test 2 208 200 11 rows selected.

 

Summary: 1. the role of regular expressions and oracle 10g Support and Follow (POSIX) Standards 2. the meta Operation character required by the regular expression. 3. several common functions of regular expressions.