Funciones de Expresiones Regulares

REGEXP_LIKE

REGEXP_LIKE(cadena, expreg)

REGEXP_LIKE is similar to the LIKE condition, except REGEXP_LIKE performs regular expression matching instead of the simple pattern matching performed by LIKE. This condition evaluates strings using characters as defined by the input character set.
This condition complies with the POSIX regular expression standard and the Unicode Regular Expression Guidelines. For more information, please refer to Appendix C, " Oracle Regular Expression Support".

regexp_like_condition::=

Description of the illustration regexp_like_condition.gif

• source_string 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_string, Oracle converts pattern to the datatype of source_string. For a listing of the operators you can specify in pattern, please refer to Appendix C, " Oracle Regular Expression Support".
• 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:
  
  • 'i' specifies case-insensitive matching.
  • 'c' specifies case-sensitive matching.
  • 'n' allows the period (.), which is the match-any-character wildcard character, to match the newline character. If you omit this parameter, the period does not match the newline character.
  • '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.
  If you specify multiple contradictory values, Oracle uses the last value. For example, if you specify 'ic', then Oracle uses case-sensitive matching. If you specify a character other than those shown above, then Oracle returns an error.
  If you omit match_parameter, then:
  
  • The default case sensitivity is determined by the value of the NLS_SORT parameter.
  • A period (.) does not match the newline character.
  • The source string is treated as a single line.
    
    
    

    See Also: "LIKE "

Examples
The following query returns the first and last names for those employees with a first name of Steven or Stephen (where first_name begins with Ste and ends with en and in between is either v or ph):

SELECT first_name, last_name
FROM employees
WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$');

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Steven               King
Steven               Markle
Stephen              Stiles

The following query returns the last name for those employees with a double vowel in their last name (where last_name contains two adjacent occurrences of either a, e, i, o, or u, regardless of case):

SELECT last_name
FROM employees
WHERE REGEXP_LIKE (last_name, '([aeiou])\1', 'i');

LAST_NAME
-------------------------
De Haan
Greenberg
Khoo
Gee
Greene
Lee
Bloom
Feeney

REGEXP_REPLACE

Syntax

Description of the illustration regexp_replace.gif


Purpose

REGEXP_REPLACE extends the functionality of the REPLACE function by letting you search a string for a regular expression pattern. By default, the function returns source_char with every occurrence of the regular expression pattern replaced with replace_string. The string returned is in the same character set as source_char. The function returns VARCHAR2 if the first argument is not a LOB and returns CLOB if the first argument is a LOB.

This function complies with the POSIX regular expression standard and the Unicode Regular Expression Guidelines. For more information, please refer toAppendix C, "Oracle Regular Expression Support".


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 ofsource_char. For a listing of the operators you can specify in pattern, please refer to Appendix C, "Oracle Regular Expression Support".


replace_string can be of any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. If replace_string is a CLOB or NCLOB, then Oracle truncates replace_string to 32K. The replace_string can contain up to 500 backreferences to subexpressions in the form \n, where n is a number from 1 to 9. If n is the backslash character in replace_string, then you must precede it with the escape character (\\). For more information on backreference expressions, please refer to the notes to "Oracle Regular Expression Support", Table C-1.


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 nonnegative integer indicating the occurrence of the replace operation:


If you specify 0, then Oracle replaces all occurrences of the match.


If you specify a positive integer n, then Oracle replaces the nth occurrence.




match_parameter is a text literal that lets you change the default matching behavior of the function. This argument affects only the matching process and has no effect on replace_string. You can specify one or more of the following values for match_parameter:


'i' specifies case-insensitive matching.


'c' specifies case-sensitive matching.


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


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


'x' ignores whitespace characters. By default, whitespace characters match themselves.



If you specify multiple contradictory values, Oracle uses the last value. For example, if you specify 'ic', then Oracle uses case-sensitive matching. If you specify a character other than those shown above, then Oracle returns an error.

If you omit match_parameter, then:


The default case sensitivity is determined by the value of the NLS_SORT parameter.


A period (.) does not match the newline character.


The source string is treated as a single line.


See Also:


REPLACE


REGEXP_INSTR, REGEXP_SUBSTR, and REGEXP_LIKE Condition








Examples

The following example examines phone_number, looking for the pattern xxx.xxx.xxxx. Oracle reformats this pattern with (xxx) xxx-xxxx.
SELECT
  REGEXP_REPLACE(phone_number,
                 '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
                 '(\1) \2-\3') "REGEXP_REPLACE"
  FROM employees;

REGEXP_REPLACE
--------------------------------------------------------------------------------
(515) 123-4567
(515) 123-4568
(515) 123-4569
(590) 423-4567
. . .



The following example examines country_name. Oracle puts a space after each non-null character in the string.
SELECT
  REGEXP_REPLACE(country_name, '(.)', '\1 ') "REGEXP_REPLACE"
  FROM countries;

REGEXP_REPLACE
--------------------------------------------------------------------------------
A r g e n t i n a
A u s t r a l i a
B e l g i u m
B r a z i l
C a n a d a
. . .



The following example examines the string, looking for two or more spaces. Oracle replaces each occurrence of two or more spaces with a single space.
SELECT
  REGEXP_REPLACE('500   Oracle     Parkway,    Redwood  Shores, CA',
                 '( ){2,}', ' ') "REGEXP_REPLACE"
  FROM DUAL;

REGEXP_REPLACE
--------------------------------------
500 Oracle Parkway, Redwood Shores, CA

select * from employees;

select first_name,  REGEXP_COUNT( first_name, '^[f-i]' ) as pattern_count from  employees;

-------
SELECT first_name, last_name  FROM employees  WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$');
---Reempaza algo
SELECT  REGEXP_REPLACE(phone_number,  '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',   '(\1) \2-\3') "REGEXP_REPLACE"  FROM employees;
SELECT  REGEXP_REPLACE(first_name,  'a',   'b') "REGEXP_REPLACE"  FROM employees;
--The following example examines country_name. Oracle puts a space after each non-null character in the string.
SELECT  REGEXP_REPLACE(country_name, '(.)', '\1 ') "REGEXP_REPLACE"   FROM countries;
--El metacaracter () porner  expresion dentro de otra 
--- REGEXP_COUNT  si se ponen en el where es para comparar si se pone en el select es para retornar 
----  En que posicion de la cadena se cumple la expresion  ejemplo le[a-c] alea retornaria 2
SELECT last_name, REGEXP_INSTR(last_name, '[aeiou]') FROM   hr.employees;
----Saca un pedazo de la cadena
SELECT last_name, REGEXP_SUBSTR(last_name, '^[AC]') FROM   hr.employees;
---i, j retorna  cuantas  la letra  cuando aparezca j veces Ana 1,2 retorna la segunda  a
---i a partir de cual numero j:cantidad de ocurrencias
SELECT last_name, REGEXP_SUBSTR ( Upper(last_name), 'A',1,2) FROM  hr.employees;
---a partir del caracter 5 hay una a
SELECT last_name, REGEXP_SUBSTR ( Upper(last_name), 'A',5,1) FROM  hr.employees;
SELECT * FROM   hr.employees where  REGEXP_SUBSTR(last_name, 'a')=hr.last_name;
----Start at 3rd position, extract 2 characters from the last ----
SELECT last_name, REGEXP_SUBSTR(last_name, '..',3) FROM   hr.employees;

---