Shulou(Shulou.com)06/01 Report--

Oracle uses four main functions of regular expressions:

1. Regexp_like can only be used for conditional expressions, similar to like, but the regular expressions are used for matching, and the syntax is simple:

2. The regexp_substr function, similar to substr, is used to pick up the character substrings described by the regular expression of the combination. The syntax is as follows:

3. Regexp_instr function, similar to instr, is used to calibrate the starting position of a character substring that conforms to the regular expression. The syntax is as follows:

4. The regexp_replace function, similar to replace, is used to replace strings that conform to regular expressions. The syntax is as follows:

The meaning of several parameters is parsed here:

1. Source_char, the input string can be a column name or a string constant or variable.

2. Pattern, regular expression.

3. Match_parameter, matching options.

Value range: I: case insensitive; c: case sensitive; n: period. Does not match newline symbols; m: multiline mode; x: extended mode, ignoring white space characters in regular expressions.

4. Position, identifying the number of characters from which the regular expression matches.

5. Occurrence to identify the matching group.

6. Replace_string, the replacement string.

-- create tables and test data

Create table tmp aswith data as (select 'like' as id,' a9999'as str from dual union allselect 'like',' a9c 'from dual union allselect' like', 'A7007' from dual union allselect 'like',' 123a34cc' from dual union allselect 'substr',' 123234345 'from dual union allselect' substr', '127c' from dual union allselect 'substr' '123456789 'from dual union allselect' instr', '192.168.0.1' from dual union allselect 'replace',' (020) 12345678 'from dual union allselect' replace', '001517729C28' from dual) select * from data The query result of SELECT * FROM tmp;-- is as follows

-- regexp_like example-- 1. Matching characters with the letter "a" is case-insensitive and matches any number\ d: matches any numeric character select str from tmp where id = 'like' and regexp_like (str,'a\ dnumbers and other characters)

Select str from tmp where id='like' and regexp_like (str,'a\ daddy')

Select str from tmp where id='like' and regexp_like (str,' ^ a\ dflowers')

SELECT str from tmp where id='like' and regexp_like (str,' ^ a\ dflowers')

-- regexp_substr example 1 str_1_1,regexp_substr (str,' [^,] +') str_1_1,regexp_substr (str,' [^,] +', 1 Magi 1) str_1_1,regexp_substr (str,' [^,] +', 1 Mague 2) str_1_2,-- occurrence matching group regexp_substr (str_2_1 [^,] +', 2 Mague 1) str_2_1-- position matches from tmpwhere id='substr' from the first character

-- regexp_substr example 2SELECTSTR Magnum REGEXPPROBSTR SUBSTR (STR,'\ d') STR,REGEXP_SUBSTR (STR,'\ dwinter, 1, 1) STR,REGEXP_SUBSTR (STR,'\ d {2}', 1, 2) STR,REGEXP_SUBSTR (STR,'\ d {3}', 2, 1) STRFROM TMPWHERE ID = 'substr'

-- regexp_instr example 1 SELECTSTR Magnum REGEXPLANTINSTR (STR,'\.') IND,REGEXP_INSTR (STR,'\.', 1,2) IND,REGEXP_INSTR (STR,'\.', 5,2) INDFROM TMPWHERE ID = 'instr'

-- regexp_instr example 2SELECTregexp_instr ('192.168.0.1' ind.', 1 minute level) ind,-- period. The location regexp_instr ('192.168.0.1 minute from dualconnect by level'\ dcoach grammar 1pm level) ind-- the position from dualconnect by level of each number

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.