Viernes, 12 Junio 2020 02:52

Expresiones Regulares en SQL

Rate this item
(0 votes)

Objetivos:

  • Utilizar Expresiones Regularesen SQL para buscar, hacer corresponder y sustituir cadenas siempre en términos de expresiones normales/regulares.
  • Ver el uso de las funciones: REGEXP_LIKEREGEXP_REPLACEREGEXP_INSTR y REGEXP_SUBSTR.

NOTA: Usamos como ejemplo la Base de Datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.


Expresiones Regulares en ORACLE

La Base de Datos Oracle 10g introduce el soporte de expresiones regulares. La implementación cumple con el estándar POSIX (Sistema Operativo Portátil para UNIX), controlado por el IEEE (Instituto de Ingenieros en Electricidad y Electrónica), para la semántica y la sintaxis de correspondencia de datos ASCII. Las capacidades multilingües de Oracle amplían las capacidades de correspondencia de los operadores más allá del estándar POSIX. Las expresiones regulares son un método para describir patrones sencillos y complejos de búsqueda y manipulación.

La manipulación y la búsqueda de cadenas suponen un amplio porcentaje de la lógica de una aplicación basada en la Web. El uso va desde la simple búsqueda de las palabras “San Francisco” en un texto especificado, pasando por la compleja extracción de todas las direcciones URL del texto, hasta la búsqueda más compleja de todas las palabras cuyo segundo carácter sea una vocal.

Si se une al SQL nativo, el uso de expresiones regulares permite operaciones muy potentes de búsqueda y de manipulación de cualquier dato almacenado en una Base de Datos Oracle. Puede utilizar esta función para solucionar fácilmente problemas que de otro modo resultarían muy complejos de programar.

Algunos Metacaracteres Útiles:

Símbolo

Descripción

*

Se corresponde con cero o más incidencias.

|

Operador de modificación para especificar correspondencias alternativas. En pocas palabras equivale a ‘o’. Eje: 8|5 = 8 o 5.

 ^/$

^: representa inicio de línea; $ es fin de línea.

[ ]

Expresión entre corchetes para una lista de correspondencia que se corresponde con cualquiera de las expresiones representadas en la lista.

{m}

Se corresponde exactamente m veces.

{m,n}

Se corresponde al menos m veces, pero no más de n veces.

[: :]

Especifica una clase de carácter y se corresponde con cualquier carácter de 
esa clase.

\

Puede tener 4 significados diferentes: 1. Se representa a sí mismo. 2. Presenta el siguiente carácter. 3. Introduce un operador. 4. No hace nada.

+

Se corresponde con una o más incidencias.

?

Se corresponde con cero o una incidencia.

.

Se corresponde con cualquier carácter del juego de caracteres soportado, excepto NULL.

()

Expresión de agrupamiento, que se trata como subexpresión única.

[==]

Especifica clases de equivalencia.

\n

Referencia a expresión anterior.

[..]

Especifica un elemento de intercalación como, por ejemplo, un elemento de varios caracteres.

\d

Equivale a un Digito (Carácter Numérico). Equivalente a [[:digit:]].

\D

Equivale a un Carácter no Numérico.

\w

Representa un carácter alfanumérico. Incluye el underscore( _)

\W

Representa un Carácter no letra.

\A

Equivale al inicio de una cadena de caracteres o el fin de una cadena antes de una nueva línea.

\Z

Representa el fin de una cadena

\s

Equivale a un espacio en blanco.

\S

Equivale a un no espacio en blando.

Los metacaracteres son caracteres especiales que tienen un significado especial como, por ejemplo, un comodín, un carácter de repetición, un carácter de no correspondencia o un rango de caracteres. Puede utilizar varios símbolos de metacaracteres predefinidos en la correspondencia de patrones.

Clases de Caracteres POSIX Útiles.

Ya indicamos que es posible usar: [::] para especificar clases de caracteres. Estas clases resultan bastante útiles en escenarios multilenguajes, esto debido a que cada lenguaje tiene un conjunto diferente de caracteres que pueden no existir en otros idiomas. El estándar POSIX ofrece las clases de carácter portátiles: '[::]' que presentamos a continuación:

Clase Carácter

Definición

[:alnum:]

Todos los caracteres alfanuméricos.

[:alpha:]

Todos los caracteres alfabéticos.

[:blank:]

Todos los caracteres de espacio en blanco.

[:cntrl:]

Todos los caracteres de control (no imprimibles).

[:digit:]

Todos los dígitos numéricos.

[:graph:]

Conjunto que agrupa estas clases: [:punct:], [:upper:], [:lower:],[:digit:].

[:lower:]

Todos los caracteres alfabéticos en minúscula.

[:print:]

Todos los caracteres imprimibles.

[:punct:]

Todos los signos de puntuación.

[:space:]

Todos los caracteres de espacio (no imprimibles).

[:upper:]

Todos los caracteres alfabéticos en mayúscula.

[:xdigit:]

Todos los caracteres hexadecimales válidos.

NotaOracle es compatible con las clases de caracteres antes presentadas, basado en la definición de clase de carácter establecidos en el parámetro de inicialización NLS_SORT.


Funciones de Expresiones Regulares

La Base de Datos Oracle 10g proporciona un juego de funciones SQL que se pueden utilizar para buscar y manipular cadenas mediante expresiones regulares. Puede utilizar estas funciones en cualquier tipo de datos que contenga datos de caracteres como, por ejemplo, CHARNCHARCLOBNCLOBNVARCHAR2 VARCHAR2. Una expresión regular debe ir entre comillas simples. Esto asegura que toda la expresión sea interpretada por la función SQL y puede mejorar la legibilidad del código. 

Nombre de Función

Descripción

REGEXP_LIKE

Parecido al operador LIKE, pero realiza una correspondencia de expresiones regulares en lugar de una correspondencia de patrones sencillos

REGEXP_REPLACE

Busca un patrón de expresión regular y lo sustituye por una cadena de sustitución

REGEXP_INSTR

Busca en una cadena especificada un patrón de expresión regular y devuelve la posición en la que se encuentra la correspondencia

REGEXP_SUBSTR

Busca un patrón de expresión regular dentro de una cadena especificada y devuelve la subcadena con la correspondencia

REGEXP_LIKE: Esta función busca un patrón en una columna de caracteres. Utilice esta función en la cláusula WHERE de una consulta para devolver las filas que se correspondan con la expresión regular que se especifique. 

REGEXP_REPLACE: Esta función busca un patrón en una columna de caracteres y sustituye cada incidencia de ese patrón por el patrón que se especifique. 

REGEXP_INSTR: Esta función busca en una cadena una incidencia especificada de un patrón de expresión regular. Hay que especificar qué incidencia se desea buscar y la posición inicial desde la que buscar. Esta función devuelve un entero que indica la posición en la cadena en la que ha encontrado la correspondencia. 

REGEXP_SUBSTR: Esta función devuelve la subcadena real que se corresponde con el patrón de expresión regular que se especifique.

Sintaxis REGEXP:

REGEXP_LIKE   (srcstr, pattern [,match_option])

REGEXP_INSTR  (srcstr, pattern [, position [, occurrence
               [, return_option [, match_option]]]])

REGEXP_SUBSTR (srcstr, pattern [, position
               [, occurrence [, match_option]]])

REGEXP_REPLACE(srcstr, pattern [,replacestr [, position
               [, occurrence [, match_option]]]])

 

Descripcion:

srcstr

Valor de búsqueda

pattern

Expresión Regular

occurrence

Incidencia que se buscará

position

Punto de partida de la búsqueda

return_option

Posición inicial o final de la incidencia

replacestr

Cadena de caracteres que sustituye al patrón

match_option

Opción para cambiar la correspondencia por defecto; puede incluir uno o más de los siguientes valores:

“c” —utiliza una correspondencia sensible a          mayúsculas/minúsculas (por defecto)

“I” —utiliza una correspondencia no sensible a     mayúsculas/minúsculas

“n” —permite el operador de correspondencia con cualquier                 carácter

“m” —trata la cadena de origen como varias líneas


Ejemplos:

CREATE TABLE tab_caracteres
(
  id_caract     NUMBER,
  caracteres    VARCHAR2(10),
  tipo_caract   VARCHAR2(8)
      CONSTRAINT ck_tip_caract
            CHECK(tipo_caract IN ('NUMERICO','LETRAS','SIGNOS','MIXTO'))
);

/*Creamos la tabla: tab_caracteres, la cual usaremos para nuestros futuros ejemplos. */

 

SET SERVEROUTPUT ON

DECLARE
  V_CARAC CHAR(87) := '@#$12345678987654abcdef~`!%^&./;:"?><ghi98jQSXCYJklmnñop*()_+=-[]\}{|6789,HRFDqrstuvwxyz';
  V_SELEC CHAR(10);
BEGIN
    FOR I IN 1..30 LOOP
    	V_SELEC :=  SUBSTR
                       (
                         V_CARAC,
                         TRUNC(
                                          DBMS_RANDOM.VALUE(1,77)
                                       ), --FIN FUNCION TRUNC
                     10); --FIN FUNCION SUBSTR

	    INSERT INTO tab_caracteres(id_caract, caracteres)
	    VALUES(I,V_SELEC);

    END LOOP;

    COMMIT;
END;

/*Usamos este Bloque Anónimo para insertar 30 registros a nuestra tabla. Los Bloques Anónimos son explicados en futuras publicaciones.*/

 

SELECT *
FROM tab_caracteres;

---OUTPUT:

/*La imagen muestra los 30 registros insertados; Notamos que el registro con el id_caracter: 2 solo contiene números; el 12 solo signos especiales; el 28 solo letras; casi todos los demás se podrían denominar mixtos, especialmente el 7, el cual posee letras, números y signos.*/

 

SELECT *
FROM tab_caracteres
WHERE REGEXP_LIKE(caracteres,'^\d+$');

/*Muestra los registros que solo tienen Caracteres Numéricos.

  • ^\d = indica que la cadena debe iniciar con números;
  • +   = indica que habrá n ocurrencias del pasado patrón.
  •   = especifica que la cadena debe concluir con el patrón que antecede el signo $.

*/

---OUTPUT:

 

SELECT *
FROM tab_caracteres
WHERE REGEXP_LIKE(caracteres,'^\D+$');

/*Muestra los registros que NO tienen Caracteres Numéricos.*/

---OUTPUT:

 

SELECT *
FROM tab_caracteres
WHERE REGEXP_LIKE(caracteres,'^\w+$')
AND REGEXP_LIKE(caracteres, '^[^[:digit:]]+$');

/*Muestra los registros que SOLO tienen Caracteres Letras.

  Como '\w' incluye números, agrego la condición de que sea no numérico: [^[:digit:]];

  Osea, que sea alfanumérico y no numérico; solo alfa= letras.

*/

---OUTPUT:

 

UPDATE tab_caracteres c
SET c.tipo_caract =
                    (SELECT
                        CASE
                            WHEN REGEXP_LIKE(c.caracteres,'^\d+$') THEN 'NUMERICO'
                            WHEN REGEXP_LIKE(c.caracteres,'^\w+$')
                              AND REGEXP_LIKE(c.caracteres, '^[^[:digit:]]+$') THEN 'LETRAS'
                            WHEN REGEXP_LIKE(c.caracteres, '^[[:punct:]]+$') THEN 'SIGNOS'
                            ELSE 'MIXTO'
                        END
                      FROM dual);

/*En este ejemplo actualizamos el campo tipo_caract con una subconsulta correlacionada; Notar el uso de la función REGEXP_LIKE combinada con un CASE para así asignar el tipo indicado a cada registro;.*/

---OUTPUT:

 

SELECT
      street_address AS "Direccion",
      REGEXP_INSTR(street_address,'[^[:alpha:]]') AS "Caracter Alfa"
FROM   locations
WHERE REGEXP_INSTR(street_address,'[^[:alpha:]]')> 1;

/*En este ejemplo, la función REGEXP_INSTR se utiliza para buscar la calle con el fin de encontrar la ubicación del primer carácter no alfabético. La búsqueda se realiza sólo en las calles que no empiecen por un número. Observe que [:<class>:] implica una clase de carácter y se corresponde con cualquier carácter de esa clase; [:alpha:] se corresponde con cualquier carácter alfabético. 

En la expresión utilizada en la consulta es: '[^[:alpha:]]':

  • [  inicia la expresión.
  • ^ indica NO.
  • [:alpha:] indica la clase de carácter alfabético.
  • ]finaliza la expresión.*/

---OUTPUT:

 

SELECT
      street_address AS "Direccion",
      REGEXP_SUBSTR(street_address , ' [^ ]+ ') AS "Calle"
FROM locations;

/*En este ejemplo, los nombres de calle se extraen de la tabla LOCATIONS. Para ello, se devuelve el contenido de la columna STREET_ADDRESS que está entre los 2 primeros espacios(null si no contiene 2 espacios) mediante la función REGEXP_SUBSTR. En la expresión utilizada en la consulta '[^ ]+ '
•  [ 
inicia la expresión.

  •  ^indica NO.
  •     indica espacio.
  •  ]finaliza la expresión.
  •  +indica 1 o más.
  •     indica espacio.*/

---OUTPUT:

 

SELECT
      caracteres,
      REGEXP_REPLACE(caracteres, '[[:upper:]]', '.') "No Mayúsculas"
FROM tab_caracteres
WHERE REGEXP_LIKE(caracteres, '^[[:alnum:]]+$');

/*El ejemplo primero filtra los registros que contienen solo caracteres alfanuméricos y en ellos reemplaza los caracteres alfabéticos en mayúscula por puntos '.'

En la expresión: '[[:upper:]]'

  •  [   = inicia la expresión.
  •   [:upper:]  =indica caracteres alfabéticos en mayúscula
  •  = finaliza la expresión.

En la expresión: '^[[:alnum:]]+$'

  •   ^[[:alnum:]]=indica el patron de inicio(alfanumérico).
  •   += indica que habrá n ocurrencias del pasado patrón.
  •   = especifica que la cadena debe concluir con el patrón que antecede el signo $.*/

---OUTPUT:


Expresiones Regulares y Restricciones de Integridad de Datos

Las Expresiones Regulares también se pueden utilizar en restricciones de control(CONSTRAINTS). Esto nos permite establecer ciertos patrones de formato para los datos que deseamos permitir en la Base de Datos.

Ejemplos:

CREATE TABLE contactos
(
  id_contacto   INTEGER,
  nombre        VARCHAR2(30)
                      CONSTRAINT ck_nombre_contact
                            CHECK
                            (
                                REGEXP_LIKE(nombre, '^[[:alpha:]]{2,5}[[:alpha:][:blank:]]+$')
                            ),
  telefono    VARCHAR2(10)
                      CONSTRAINT ck_telefono_contact
                            CHECK
                            (
                                REGEXP_LIKE(telefono, '^8[024]9[[:digit:]]{7}')
                            ),
  correo      VARCHAR2(50)
                      CONSTRAINT ck_correo_contact
                            CHECK
                            (
                               REGEXP_LIKE(correo, '^[[:alpha:]][[:alnum:]]{1,25}@[[:alpha:]]{5,19}.[[:alpha:]]{2,3}')
                            )
);

/*Para visualizar el uso de Expresiones Regulares en Restricciones de Integridad de datos creamos la tabla contactos. En ella creamos restricciones en los campos: 'nombre', 'teléfono' y 'correo'.

  • Para el 'nombre' los usuarios solo podrán digital caracteres alfabéticos y/o espacios en blanco:

^[[:alpha:]]{2,5} = que inicie con por lo menos 2 caracteres alfabéticos.
[[:alpha:][:blank:]]+$ = que contenga caracteres alfabéticos o espacios, hasta el final.

  • Para el'teléfono' solo se podrá digital números de 10 dígitos que inicien con 809, 829 o 849:

^8 = que inicio con un 8.
[024] = permite uno de estos tres dígitos: 0, 2 o 4.
9 = debe haber un 9 en esta posición.
[[:digit:]]{7} = indica la presencia de 7 dígitos numéricos seguidos.

  • Para el 'correo', las entradas validas iniciaran con un carácter alfabético, seguido por un máximo de 25 caracteres alfanuméricos(mínimo 1), seguidos por una @, luego un máximo de 19 caracteres alfabéticos(mínimo 5), luego un punto '.' y por último un máximo de 3 caracteres alfabéticos(mínimo 2).

^[[:alpha:]]  = inicia con un carácter alfabético.
[[:alnum:]]{1,25} = de 1 a 25 caracteres alfanuméricos.
@  = presencia de una Arroba.
[[:alpha:]]{5,19}  = de 5 a 19 caracteres alfabéticos.
.  = presencia de un punto.
[[:alpha:]]{2,3}   = de 2 a 3 caracteres alfabéticos.
*/

 

INSERT INTO contactos(nombre)  --NO Valido
VALUES('  Juan');

INSERT INTO contactos(nombre) --Valido
VALUES('Lily Perez');

INSERT INTO contactos(nombre)  --NO Valido
VALUES('1Manuel Perez');

INSERT INTO contactos(nombre) --Valido
VALUES('Jose');

/*Estos son ejemplos de entradas validas y no validas para el campo 'nombre'.*/

 

INSERT INTO contactos(telefono)  --Valido
VALUES('8499542357');

INSERT INTO contactos(telefono)  --Valido
VALUES('8099542357');

INSERT INTO contactos(telefono)  --NO Valido
VALUES('8399542357');

INSERT INTO contactos(telefono)  --NO Valido
VALUES('8499542');

/*Estos son ejemplos de entradas validas y no validas para el campo 'telefono'.*/

 

INSERT INTO contactos(correo)   --NO Valido
VALUES(Esta dirección de correo electrónico está siendo protegida contra los robots de spam. Necesita tener JavaScript habilitado para poder verlo.');

INSERT INTO contactos(correo)  --Valido
VALUES(Esta dirección de correo electrónico está siendo protegida contra los robots de spam. Necesita tener JavaScript habilitado para poder verlo.');

INSERT INTO contactos(correo)   --NO Valido
VALUES(' Esta dirección de correo electrónico está siendo protegida contra los robots de spam. Necesita tener JavaScript habilitado para poder verlo.');

INSERT INTO contactos(correo)   --Valido
VALUES(Esta dirección de correo electrónico está siendo protegida contra los robots de spam. Necesita tener JavaScript habilitado para poder verlo.');

/*Estos son ejemplos de entradas validas y no validas para el campo 'correo'.*/


Link con un Archivo de ejercicios de práctica!!!


Fuente: Base de Datos Oracle 10g: Conceptos Fundamentales de SQL 1

Read 1532 times Last modified on Jueves, 09 Julio 2020 17:27

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.

Magic PL/SQL

Blog orientado al desarrollo de PL/SQL en el "Maravilloso Mundo ORACLE". Cursos Online y Tutoriales Gratis.