Oracle REGEXP Funciones | Bases de datos, Oracle

Oracle Database 10g ofrece cuatro funciones de expresiones regulares. Se pueden utilizar en SQL y PL / SQL. Sus utilidades son las siguientes:

Búsqueda de texto. Las expresiones regulares nos permiten hacer búsquedas complejas de cadenas de caracteres dentro de las columnas de nuestras tablas.
Formateo de datos. Podemos modificar los datos de las sentencias SELECT, invirtiendo palabras, agregando o quitando caracteres, etc.
Definición de constraints. A fin de forzar el contenido de una columna para que se ajuste a un formato determinado: casilla de correo, número telefónico, etc.
Manipulación de datos.

Antes de seguir leyendo esto, si no entendeís muy bien los patrones de búsqueda, echadle un vistazo a esto.

Entender los patrones de búsqueda o expresiones regulares

REGEXP_LIKE (cadena, expresiones regulares, modo).
Se puede utilizar en el WHERE y HAVING de una instrucción SELECT, en una secuencia de comandos de PL/SQL (devuelve un valor booleano) y también, se puede utilizar en una restricción CHECK.
La expresión regular se comparara con la cadena. El parámetro modo es opcional.

SELECT * FROM prueba DONDE REGEXP_LIKE (columna, 'expresion', 'i');
IF REGEXP_LIKE ('cadena', 'expresion') THEN
 / * CODIGO * / 
ELSE
 / * CODIGO * / 
END IF;
ALTER TABLE prueba ADD (columna mycolumn_regexp CHECK (REGEXP_LIKE (columna, '^ $ expresion')));

Por ejemplo si busco un formato tipo 12.1234567.12, la expresión seria del 0-9 en las primeras dos posiciones, del 0-9 en las siguientes siete y el final del 0-9 las dos últimas, entre medias los carácteres que sean.

IF NOT REGEXP_LIKE('&amp;&amp;CodCotizDestino', '[0-9]{2}/[0-9]{7}/[0-9]{2}') THEN
   RAISE FORMATO;
END IF;

Por ejemplo para un CIF, el primer carácter {1} debería tener ABCDEFGHKLMNPQS, el los siguientes siete del cero al nueve y la última posición lo que sea (.)

IF NOT REGEXP_LIKE(CIF, '[ABCDEFGHKLMNPQS]{1}[0-9]{7}.') THEN

En esta obligamos a que empiece por esas letras.

IF REGEXP_LIKE(CIF, '^[ABCDEFGHKLMNPQS]') THEN

REGEXP_SUBSTR (cadena, expresiones regulares, posicion, ocurrencia, modo) Devuelve la cadena que cumple la expresión regular. NO se parece a SUBSTR.
Si no coinciden devuelve NULL.
Es posible utilizar REGEXP_SUBSTR con una sola cadena o con una columna.
Se puede utilizar en las cláusulas SELECT para recuperar sólo una parte de una columna.
El parámetro posición especifica la posición desde la que empieza a buscar. El primer carácter tiene la posición 1.
El parámetro de ocurrencia especifica a partir de que aparición tiene que devolver la cadena. Se pone 1, para encontrar la primera aparición de la cadena-
Los últimos tres parámetros son opcionales.

SELECT REGEXP_SUBSTR (micolumna, 'expresion') FROM prueba;
valor: = REGEXP_SUBSTR ('cadena', 'expresion', 1, 1, 'i');

Por ejemplo tenemos un PLSQL que realizará un update de varios campos con los datos de un parámetro:
El parámetro es : 22.333.444.555 (Parametro);
Actualizará CAMPO1 con 22, CAMPO2 = 333, CAMPO3 = 444 y CAMPO4 = 555.

Por ejemplo tenemos un PLSQL que realizará un update de varios campos con los datos de un parámetro:
El parámetro es : 22.333.444.555

Actualizará CAMPO1 con 22, CAMPO2 = 333, CAMPO3 = 444 y CAMPO4 = 555.

DECLARE
   TYPE id_t IS TABLE OF PRUEBA.ID%TYPE;
   TYPE codigo_t IS TABLE OF CODIGO.CDDUP%TYPE;
   TYPE clase_t IS TABLE OF CLASE.CDCLASNM%TYPE;
   e_id id_t;
   e_codigo codigo_t;
   e_clase clase_t;
 
BEGIN
   UPDATE PRUEBA SET  CAMPO1 = REGEXP_SUBSTR('&amp;&amp;Parametro','[^.]+',1,1),
                   CAMPO2 =  REGEXP_SUBSTR('&amp;&amp;Parametro','[^.]+',1,2),
                   CAMPO3 =  REGEXP_SUBSTR('&amp;&amp;Parametro','[^.]+',1,3),
                   CAMPO4 =   REGEXP_SUBSTR('&amp;&amp;Parametro','[^.]+',1,4)
   RETURNING  ID, CODIGO, CLASE
   BULK COLLECT INTO e_id, e_codigo, e_clase;
 
  FOR i IN 1 .. SQL%ROWCOUNT LOOP
    DBMS_OUTPUT.PUT_LINE(' ID ' ||e_id(i) || ' CODIGO ' || e_codigo(i) ||
    ' CLASE ' || e_clase(i));
  END LOOP;
  COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('ERROR: ' || substr(sqlerrm, 1,80));
END;
/

Si no habeís entendido la sentencia UPDATE, hago que me devuelva las columnas ID, CODIGO, CLASE con RETURNING que han sido modificadas y las meto en las variables declaradas.
Luego en un for las escribo para verlas.

Algunos ejemplos de expresiones regulares:
Cualquier letra en minuscula [a-z]
Entero ^(?:\+|-)?\d+$

Correo electrónico

SELECT REGEXP_SUBSTR('prueba@gmail.com','^[a-zA-Z0-9!#$%''\*\+-/=\?^_`\{|\}~]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}$',1,1) FROM dual;

Nos devolverá el mail si es correcto.

Dirección IP
‘([[:digit:]]{1,3}).([[:digit:]]{1,3}).([[:digit:]]{1,3}).([[:digit:]]{1,3})’

URL
^(ht|f)tp(s?)\:\/\/[0-9a-zA-Z]([-.\w]*[0-9a-zA-Z])*(:(0-9)*)*(\/?)( [a-zA-Z0-9\-\.\?\,\’\/\\\+&%\$#_]*)?$

Fecha
^\d{1,2}\/\d{1,2}\/\d{2,4}$
(Por ejemplo 01/01/2007)
Hora ^(0[1-9]|1\d|2[0-3]):([0-5]\d):([0-5]\d)$
(Por ejemplo 10:45:23)

Número tarjeta de crédito
^((67\d{2})|(4\d{3})|(5[1-5]\d{2})|(6011))(-?\s?\d{4}){3}|(3[4,7])\ d{2}-?\s?\d{6}-?\s?\d{5}$

Número teléfono
^[0-9]{2,3}-? ?[0-9]{6,7}$

Código postal
^([1-9]{2}|[0-9][1-9]|[1-9][0-9])[0-9]{3}$

Certificado Identificación Fiscal
^(X(-|\.)?0?\d{7}(-|\.)?[A-Z]|[A-Z](-|\.)?\d{7}(-|\.)? [0-9A-Z]|\d{8}(-|\.)?[A-Z])$

REGEXP_REPLACE (cadena, expresiones regulares, sustituir por, la posición, la aparición, modo).
Devuelve la cadena de origen con una o todas las expresiones regulares reemplazadas.
Si se especifica un número positivo para la aparición se sustituye sólo esa aparición.
Si se especifica cero o se omite el parámetro, todas son reemplazadas.
Los últimos tres parámetros son opcionales.

SELECT REGEXP_REPLACE (columa, 'expresion', 'reemplazarpor') FROM mitabla;
resultado: = REGEXP_REPLACE (cadena, 'expresion',' reemplazo ', 1, 0,'i');
 
SELECT
   REGEXP_REPLACE('91.356.71.81', '([[:digit:]]{2})\.([[:digit:]]{3})\.([[:digit:]]{2})\.([[:digit:]]{2})', '(\1) \2-\3-\4') "REGEXP_REPLACE"
FROM dual;  2    3
 
REGEXP_REPLACE
--------------
(91) 356-71-81
 
SELECT REGEXP_REPLACE('H1234 H4321 H2345','(.*) (.*) (.*)','\3, \2 \1')FROM dual;
 
REGEXP_REPLACE('H1
------------------
H2345, H4321 H1234

REGEXP_INSTR (de origen, expresiones regulares, la posición desde que empezaría a buscar, frecuencia, return_option, modos) Esta función permite localizar la ubicación de partida de una subcadena (expresión regular) dentro de una cadena. Esta función tiene los mismos parámetros que REGEXP_SUBSTR, más el return_option. Los últimos 4 parámetros son opcionales.

Establecer return_option a cero o se omite el parámetro para obtener la posición del primer carácter en el partido. Póngalo en una para obtener la posición del primer carácter después del partido. El primer carácter de la cadena tiene la posición 1. REGEXP_INSTR devuelve cero si la cadena no se puede encontrar.

SELECT REGEXP_INSTR (mycolumn, 'regexp', 1, 1, 0, 'i') FROM mitabla;

REGEXP_COUNT (A partir de la 11g)
devuelve el número de veces que el patrón de búsqueda aparece en cadena de origen.

SQL&gt; SELECT REGEXP_COUNT ('123 123 123 123 ', '123', 1, 'i') de la doble;
 
 REGEXP_COUNT ('123123123123 ', '123', una 'I')
 ----------------------------------------
                                        4
 Una fila seleccionada.
 
 SQL&gt; SELECT REGEXP_COUNT ('123 123 123 123 ', '123', 9 'i') de la doble;
 
 REGEXP_COUNT ('123123123123 ', '123', 9 'I')
 ----------------------------------------
                                        2
 
 Una fila seleccionada.

Estos son los parámetros que podéis utilizar para el modo (última posición).

i: case insensitive
c: case sensitive
n: Donde (.) se corresponde con el salto de linea.
m: Indicamos que el string tiene varias lineas.