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, … 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
Adrián Zengotitabengoa
programmer and sysadmin. Passionate about new technologies with great interest in learning new knowledge.
Latest posts by Adrián Zengotitabengoa (see all)
- Expresiones regulares en Oracle - 7 February, 2019
