Oracle Regular Expression functions-Summary

Oracle Regular Expression Functions

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

 

-- Return the consumer whose FIRST_NAME was created starting with 'J' from 1965-1968.

SELECT FIRST_NAME, DOB

FROM MERs

WHERE REGEXP_LIKE (TO_CHAR (DOB, 'yyyy'), '^ 196 [5-8] $ ')

AND REGEXP_LIKE (FIRST_NAME, '^ J ');

 

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.

 

-- 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.

SELECT REGEXP_INSTR ('But, soft! What light through yonder window breaks? ', 'L [[: alpha:] {4}', 1, 1, 0) from dual;

 

-- 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.

SELECT REGEXP_INSTR ('But, soft! What light through yonder window breaks? ', 'L [[: alpha:] {4}', 1, 1) from dual;

 

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

 

-- Return But, soft! What XXX through yonder window breaks? Use 'xxx' to replace 'light'

SELECT REGEXP_REPLACE ('But, soft! What light through yonder window breaks? ', 'L [[: alpha:] {4}', 'xxx') from dual;

 

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

 

-- Return 'light'

SELECT REGEXP_SUBSTR ('But, soft! What light through yonder window breaks? ', 'L [[: alpha:] {4}') from dual;