Oracle Regular Expression Functions

Oracle Regular Expression Function 1. The metacharacters in the regular expression indicate that the character to be matched is a special character, constant, or referenced by the latter. (Repeat the previous match later) n match linefeed match (MATCH () Match) ^ match the start position of the string if A is the first character of the string, ^

Oracle Regular Expression Function 1. Example of metacharacters in a regular expression/Description of the character to be matched is a special character, constant, or referenced by the latter. (Repeat the previous match later)/n match line break // match // (MATCH (/) Match) ^ match the start position of the string if A is the first character of the string, ^

Oracle Regular Expression Functions

1. metacharacters in Regular Expressions

Metacharacters	Meaning	Example
/	It indicates that the character to be matched is a special character, constant, or referenced by the latter. (Repeat the previous match later)	/N match the linefeed // Match/ /(MATCH ( /) Match)
^	Match the start position of a string	If A is the first character of A string, ^ A matches.
$	Match the end of a string	If B is the last character of the string, $ B matches B.
*	Match the previous character 0 or multiple times	Ba * rk can match brk, bark, baark, etc.
+	Match the previous character once or multiple times	Ba + rk can match bark, baark, and so on, but cannot match brk, that is, at least once.
?	Match the first character 0 or 1 time	Ba? Rk can match bark, brk, and so on, but cannot match baark.
{N}	Match the previous character EXACTLY n times, where n is an integer	Hob {2} it can match hobbit
{N, m}	Match the first character at least n times, at most m times, where n and m are integers	Hob {2, 3} it can match hobbit or hobbbit
.	Match any single character except null	In hob. it,. can be any single character, such as hobsit.
(Pattern)	In parentheses, pattern is a subregular expression that matches a specified pattern.	For example, aaa (x | y) can match aaax or aaay.
X | y	Match "or"	X | y can match x or y.
[Abc]	It can match any single character in abc.	Hello [abc] can match helloa, hellob, helloc
[A-z]	Match any single character in the specified range	Hell [a-z] can match hello or hellz
[:]	Specifies a character class that can match any character in the class	[: Alphanum:] can match 0-9, A-Z, a-z [: Alpha:] matching characters A-Z and a-z [: Blank:] can match spaces or the tab key [: Digit:] numbers 0-9 can be matched [: Graph:] can match non-null characters [: Lower:] can match lowercase letters a-z [: Print:] is similar to [: graph:]. The difference is that [: print:] contains space characters. [: Punct:] can match punctuation marks., "", etc. [: Space:] can match all null characters [: Upper:] can match uppercase letters A-Z [: Xdigit:] It can match hexadecimal numbers 0-9, A-F, a-f
/N	This is a post reference for the previous match hit, where n is a positive integer	(.)/1 can match two consecutive non-null characters. (.) It can match any single character except null, AND/1 repeat the content of the last match, that is, it matches the same character again, so it can match two consecutive and identical non-null characters

2. REGEXP_LIKE (x, pattern [, match_option]) is used to find the Regular Expression pattern in x. This function also provides an optional parameter match_option string to indicate the default matching options. The value of match_option is as follows:
'C' indicates that the matching time zone is case sensitive (default );
'I' indicates that the matching is case insensitive;
'N' allows operators that can match any character;
'M' uses x as a string that contains multiple rows.

SQL code

1. DECLARE
2. V_FIRST_NAME VARCHAR2 (50 );
3. V_DOB DATE;
4. BEGIN
5. -- Return the consumer whose FIRST_NAME was created starting with 'J' from 1965-1968.
6. SELECT FIRST_NAME, dob into V_FIRST_NAME, V_DOB
7. FROM MERs
8. WHERE REGEXP_LIKE (TO_CHAR (DOB, 'yyyy'), '^ 196 [5-8] $') AND REGEXP_LIKE (FIRST_NAME, '^ J ');
10. DBMS_OUTPUT.PUT_LINE (V_FIRST_NAME );
12. END;

3. REGEXP_INSTR (x, pattern [, start [, occurrence [, return_option [, match_option]) is used to find pattern in x. Returns the position where pattern appears in x. The matching position starts from 1. You can refer to the string function INSTR (), parameter-related:
'Start' start position;
'Opcurrence 'indicates the location where the pattern appears for the first time;
'Eturn _ option' indicates the integer to be returned. If this parameter is set to 0, the integer to be returned is the position of one character in x. If this parameter is not a 0 integer, the return integer is the position of the character after pattern in x;
'Match _ option' modifies the default matching settings.

SQL code

1. DECLARE
2. V_RESULT INTEGER;
3. BEGIN
4. -- Return 17 to locate the first occurrence position of a word starting with l followed by four arbitrary letters in the first parameter. Here is the position of l in light.
5. SELECT REGEXP_INSTR ('But, soft! What light through yonder window breaks? ', 'L [[: alpha:] {4}', 1, 1, 0) INTO V_RESULT
6. From dual;
8. DBMS_OUTPUT.PUT_LINE (V_RESULT );
10. -- Returns 22 and finds the first occurrence position of the word starting with l followed by four arbitrary letters in the first parameter. Here, the t position in light is + 1.
11. SELECT REGEXP_INSTR ('But, soft! What light through yonder window breaks? ', 'L [[: alpha:] {4}', 1, 1) INTO V_RESULT
12. From dual;
14. DBMS_OUTPUT.PUT_LINE (V_RESULT );
15. END;

4. REGEXP_REPLACE (x, pattern [, replace_string [, start [, occurrence [, match_option]) searches for pattern in x and replaces it with replae_string. You can refer to the string function REPLACE (), the parameter is the same as the REGEXP_INSTR function, refer to 3rd

SQL code

1. DECLARE
2. V_RESULT VARCHAR2 (255 );
3. BEGIN
4. -- Return But, soft! What XXX through yonder window breaks? Use 'xxx' to replace 'light'
5. SELECT REGEXP_REPLACE ('But, soft! What light through yonder window breaks? ', 'L [[: alpha:] {4}', 'xxx') INTO V_RESULT
6. From dual;
7. DBMS_OUTPUT.PUT_LINE (V_RESULT );
8. END;
   

5. REGEXP_SUBSTR (x, pattern [, start [, occurrence [, match_option]) is used to search for pattern in x and return it. Refer to the string function SUBSTR (). The parameters are the same as those of the REGEXP_INSTR function. Refer to 3rd

SQL code

1. DECLARE
2. V_RESULT VARCHAR2 (255 );
3. BEGIN
4. -- Return 'light'
5. SELECT REGEXP_SUBSTR ('But, soft! What light through yonder window breaks? ', 'L [[: alpha:] {4}') INTO V_RESULT
6. From dual;
7. DBMS_OUTPUT.PUT_LINE (V_RESULT );
8. END;