En ocasiones cuando queremos buscar determinada información mediante sentencias SQL u Oracle que cumplan un patrón fijo utilizamos la expresión LIKE.

Aun así más de una vez nos habremos topado con la situación de tener que buscar un patrón de texto que siga una serie de reglas concretas o que nos hayamos visto en el caso de tener que realizar una búsqueda ignorando mayúsculas o minúsculas,  entre otros casos. A pesar de que para esta tarea hay funciones predefinidas (Upper o Lower) la ventaja de utilizar expresiones regulares es clara al permitir resolver todas estas situaciones con una sola consulta.

Este tipo de expresiones más avanzadas suelen utilizarse para localizar grandes flujos de texto en base de datos, así como también manipular la información extraída. En este artículo aprenderás a manejar las expresiones regulares en Oracle. Las funciones que se detallan están disponibles a partir de la versión 10g de Oracle Database.

Expresiones regulares en Oracle

Tabla de operadores:

Los operadores se utilizan para establecer el patrón de texto que queremos buscar en base de datos. Antes de continuar con las funciones REGEXP conviene revisar esta tabla.

Operador Significado
. Comodín. Cualquier carácter. Solo uno.
+ 1 o más ocurrencias. Se utiliza en conjunto con un carácter o palabra que precede al símbolo. Por ejemplo si escribimos “a+” localizará todas las palabras que tengan una o más aes.
* Comodín. Sirve para completar cualquier palabra. Indica 0 o varias ocurrencias de la cadena o carácter que le precede.

Ejemplo: (asset)*

? 0 o 1 ocurrencia de la expresión que le precede al símbolo.

Ejemplo:

  h? : información que contenga cero o una h

[…] Conjunto de caracteres. Todo lo que indiquemos dentro de los corchetes hará pattern matching una vez con las expresiones que coincidan en base de datos.

Ejemplos:

  [trn]: expresiones que contengan las letras t, r o n

[034]: expresiones que contengan entre sus cadenas los  números 0, 3 o 4

  [1-9]: expresiones que tengan números del 1 al 9

  [a-z] expresiones que tengan letras de la a la z

  [4-8d-pA-Z] : que contengan dígitos del 4 al 8, alguna de las letras de la d hasta la p (en minúsculas), y letras de la A a la Z en mayúsculas.

[^…] No contenido en lista de caracteres.

Ejemplo:

[^12d]: expresiones que no contengan el número 1, el número 2 o la letra d

{num} Comprueba que haya cadenas con solo num ocurrencias de la expresión que le precede.

Ejemplo: (bnb){2} comprueba que hay exactamente 2 ocurrencias  de la palabra BNB en los resultados de búsqueda. Es decir, entre ellos tienen que haber filas que tengan la secuencia “bnbbnb”.

{num,} Lo mismo que en el anterior caso, pero con la diferencia de que comprueba que hay al menos num ocurrencias de la expresión que le precede.

Ejemplo: 1{2,} comprueba que hay al menos 2 ocurrencias  del número 1.

  

   · Secuencia correcta: 111

   · Secuencia incorrecta: 1

   · Secuencia correcta: 11

{num, x} Al menos num ocurrencias y no más de x ocurrencias.

Ejemplo: a{2,3} . Entre 2 y 3 aes

Secuencia correcta: aa

Secuencia correcta: aaa

Secuencia incorrecta: aaaa

() Subexpresión. A diferencia de los corchetes los paréntesis sirven para englobar conjuntos de caracteres (palabras) que deben aparecer en la consulta.

Ejemplo: ( ‘Accounts Payable’ | ‘Billing’)

| Operador or. Indica una expresión u otra.
Intervalo.
Ejemplo: t-r, 4-2
^ Principio de línea. Sirve para buscar palabras que se encuentren al principio de una línea de un campo.

Ejemplo: ^abc[^n] : filas de la base de datos que tengan información de palabras que tengan como abc en su principio de línea y que a continuación no contengan la letra n.

$ Final de línea. Indica que la expresión que le precede se tiene que encontrar al final de línea.

Ejemplos:

 a$

 factura$

\ Símbolo de escape. Interpreta cualquier carácter especial como un literal. Se utiliza para no interpretar los operadores y tomarlos como un resultado de búsqueda más.

Ejemplos: \$, \|,\[, \\

\s Cualquier espacio en blanco
\S Cualquier caracter que no sea un espacio
[[:class:]] Operador de clase. Verifica que las filas o las cadenas de texto cumplen la condición de pertenecer a alguno de los elementos de la clase especificada. Las clases que se pueden introducir en la condición de la consulta pueden ser las siguientes:

[[:alnum:]] – Todos los carácteres alfanuméricos (Números y letras). Obtiene las filas en cuya columna figure cualquier letra o cualquier número.

[[:alpha:]] – Todas las letras del abecedario

[[:blank:]] – Todos los espacios en blanco

[[:cntrl:]] – Carácteres de control que no se pueden imprimir por pantalla.

[[:digit:]] – Números

[[:xdigit:]] – Carácteres en hexadecimal

[[:punct:]] – Signos de puntuación

[[:upper:]] – Palabras en mayúsculas
[[:lower:]] – Palabras en minúsculas

[[:graph:]] – Localiza toda información que tenga signos de puntuación, palabras en mayúsculas, palabras en minúsculas y dígitos. Es una agrupación de las clases [[:punct:]], [[:upper:]], [[:lower:]], [[:digit:]]

[[:print:]] – Carácteres que se pueden mostrar por pantalla

 

REGEXP LIKE:

Localiza todas filas cuyo valor almacenado en la columna corresponde a un patrón de texto no fijo.

Sintaxis:

Select [Column1, Column2, …]

From t1, t2, …
Where
   REGEXP_LIKE(columna_o_cadena_origen, patrón_búsqueda, comportamiento_por_defecto)

Donde:

 · columna_o_cadena_origen: columna o texto donde queremos buscar un patrón.

 · patrón_búsqueda: cadena que queremos buscar en la tabla.

 · comportamiento_por_defecto (opcional):

  > ‘i’: case-insensitive. Búsqueda ignorando mayúsculas o minúsculas.

  > ‘c’: case sensitive. Sensible a mayúsculas y minúsculas.

  > ‘n’: ‘newline’. Al marcar esta opción permites que cuando se utilice el comodín ‘.’ se haga pattern-matching con los saltos de línea.

  > ‘m’: “multi-line”. Interpreta el texto en varias líneas.

 

Ejemplo 1. Búsqueda ignorando mayúsculas y minúsculas de todos los nodos de un árbol cuya descripción comience por cualquier texto y termine en “ones”:

SELECT DISTINCT DESCR

FROM PS_TREE_NODE_TBL

WHERE REGEXP_LIKE(DESCR, '^*ONES$', 'i')

 

Ejemplo 2. Todos los proyectos cuyo nombre contenga las siglas BNB o AP y que terminen en 94.

SELECT PROJECTNAME, PROJECTDESCR
FROM PSPROJECTDEFN
WHERE REGEXP_LIKE(PROJECTNAME, '(BNB|AP)*94$')

 

REGEXP REPLACE:

Reemplaza la fila o cadena de texto que cumple un patrón ocurrencia por otro texto.

 

Ejemplo:

Reemplazar en todas las facturas que dispongan de id de recepción, y cuya descripción siga el patrón “… NUC … / …”, el identificador de pedido siempre y cuando el pedido contenga entre sus dígitos el número 52.

SELECT VOUCHER_ID, DESCR, PO_ID, REGEXP_REPLACE(PO_ID, '52','XX' ) “PO_MODIFICADO”

FROM PS_VCHR_MTCH_DETLS

WHERE RECEIVER_ID IS NOT NULL

   AND REGEXP_LIKE(DESCR,'([[:alnum:]])*(NUC)*/','c')

 

 

REGEXP SUBSTR:

Localiza dentro de una columna la subcadena que cumpla con el patrón indicado a continuación, indicando también la cantidad de caracteres que devuelve el resultado:

  SELECT REGEXP_SUBSTR('0091-222-222|908', '*\|[^\S]*$') || '  Long.:'

  ||  Length(REGEXP_SUBSTR('0091-222-222|908', '*\|[^\S]*$')) "Cadena reemplazada"

  FROM DUAL

 

REGEXP COUNT:

Cuenta el número de veces que aparece un determinado patrón de texto en una columna. Si especificamos más de un patrón sumará las cantidades localizadas para cada uno de los patrones.

Ejemplo:

SELECT 'Ocurrencia patrón @2: ' || ' : ' ||  REGEXP_COUNT('Esto es una columna de texto. @2@4@5-2@4@7@8@25-5-_x_t','@2') 

, 'Ocurrencias del patrón 5-' || ' : ' ||  REGEXP_COUNT('Esto es una columna de texto. @2@4@5-2@4@7@8@25-5-_x_t','(5-)')

 ,'Ocurrencias del patrón @2 + Ocurrencias del patrón 5-' || ' : ' ||

REGEXP_COUNT('Esto es una columna de texto. @2@4@5-2@4@7@8@25-5-_x_t','(@2|5-)')

FROM DUAL

Otras querys de utilidad

Aunque esta query no utiliza REGEXP es interesante ya que podemos localizar cualquier texto en base de datos ignorando acentos, minúsculas o mayúsculas. Para ello utilizamos la función convert que convierte el contenido del campo a un conjunto de caracteres ascii:

          Ejemplo con los departamentos:

SELECT * 
FROM PS_DEPT_TBL
WHERE Upper(Convert(DESCR, 'US7ASCII')) Like  Upper(Convert('%' || (SELECT SubStr(‘TeXTo a BUscar aquí', 1, 10) FROM DUAL ) 
 || '%' , 'US7ASCII') )

Enlaces recomendados:

Using Regular Exoressions in Oracle Database

Introducción al uso de las Expresiones Regulares en una Base de Datos Oracle

Using Regular Expressions with Oracle Database

POSIX Extended Regular Expressions for Oracle

REGEXP_COUNT

Adrián Zengotitabengoa

Adrián Zengotitabengoa

PeopleSoft Technical Consultant Junior at BNB
Adrian is a Technical Consultant Jr in PeopleSoft,
programmer and sysadmin. Passionate about new technologies with great interest in learning new knowledge.
Adrián Zengotitabengoa

Latest posts by Adrián Zengotitabengoa (see all)