PostgreSQL es una popular base de datos relacional de código abierto con una sólida reputación por su confiabilidad, flexibilidad y soporte para estándares técnicos abiertos. Admite tipos de datos relacionales y no relacionales, lo que lo convierte en una de las bases de datos relacionales más cumplidas, estables y maduras.
En esta guía, nos sumergiremos profundamente en el uso de Regex con PostgreSQL.
Prerrequisitos:
Para realizar los pasos demostrados en esta guía, necesita los siguientes componentes:
Un sistema de Linux concurrido correctamente. Obtenga más información sobre cómo crear una máquina virtual de Ubuntu en Virtualbox.
Una instalación adecuada de PostgreSQL. Obtenga más información sobre cómo instalar y configurar PostgreSQL en Ubuntu.
Acceso a un no-raíz usuario sudo Obtenga más información sobre cómo administrar el privilegio de sudo usando sudoers.
Regex y postgresql
Un corto plazo para expresión regular, Regex es una cadena de texto que describe un patrón para que coincida, localizar y administrar el texto. Es una herramienta versátil que es compatible con muchos lenguajes de programación (Perl, Python, Java, Kotlin, Scala, GO, C ++, etc.) y herramientas (Awk, Sed, Nginx, Grep/Egrep y más).
PostgreSQL utiliza las expresiones regulares de Posix que pueden funcionar mejor que las consultas como COMO y SIMILAR A operadores.
Base de datos de demostración
Para fines de demostración, vamos a trabajar con una base de datos ficticia que está poblada con entradas ficticias.
Conectarse al servidor local de PostgreSQL como post -put:
$ sudo -i -u postgres psql
Desde el shell PostgreSQL, cree una base de datos ficticia llamada demo_db:
$ Crear base de datos demo_db;
Cambiar a la nueva base de datos:
$ \ c demo_db
Crea una nueva tabla:
Crear usuarios de tabla ( ID Clave primaria en serie, Texto de nombre no nulo, Correo electrónico de correo electrónico);
A continuación, llene la tabla con algunos valores:
Aquí hay una breve lista de varios operadores Regex Postgrex para la coincidencia de patrones:
~: Un operador común para que coincida con una expresión regular (sensible a la caja)
~*: Coincide con la expresión regular dada (insensible al caso)
!~: Filtra los sucesos inigualables (sensibles a la caja)
!~*: Filtros los sucesos inigualables (caso insensible)
Ejemplo 1: una introducción
Comencemos nuestro viaje con la expresión regular de PostgreSQL con el siguiente ejemplo:
$ Select * de los usuarios donde el correo electrónico ~ '^.ps
Aquí:
La expresión regular comienza con el operador Tilde (~) seguido de un comodín (*). Selecciona todos los registros de la tabla de usuarios.
El operador "^" denota el inicio de una cadena.
El ".El operador es un operador comodín para un solo personaje.
El operador "$" denota el final de una cadena.
Todos estos operadores juntos, “^.*$ ”, Denota una cadena que comienza con cualquier personaje y termina con cualquier cadena.
Ejemplo 2: Sensible en mayúsculas
Por defecto, el operador tilde (~) es sensible a la caja. Podemos demostrar esta propiedad utilizando las siguientes consultas:
$ Select * de los usuarios donde el correo electrónico ~ 'ejemplo';
$ Select * de los usuarios donde el correo electrónico ~ 'ejemplo';
Ejemplo 3: Insensible en el caso
Si queremos realizar las operaciones insensibles a la caja, debemos incluir el operador Asterisk (*) junto con el operador TILDE (~).
$ Select * de los usuarios donde el correo electrónico ~ * 'ejemplo';
$ Select * de los usuarios donde el correo electrónico ~ * 'ejemplo';
Dado que especificamos la interpretación insensible al caso de la expresión regular, ambas consultas devuelven la misma salida.
Ejemplo 4: invertir coincidencia
Por defecto, las expresiones regulares coinciden con el patrón especificado.
Eche un vistazo a la siguiente consulta:
$ Select * de los usuarios donde el correo electrónico !~ 'Ejemplo';
$ Select * de los usuarios donde el correo electrónico !~ 'Ejemplo';
Aquí:
El comportamiento predeterminado del operador "~" es coincidir con el patrón especificado de regex.
Utilizando el "!~ ”Operador, ignoramos los casos en que el patrón coincide.
Para realizar la coincidencia invertida de la caja, use el "!~*”Operador en su lugar:
$ Select * de los usuarios donde el correo electrónico !~* 'Ejemplo';
$ Select * de los usuarios donde el correo electrónico !~* 'Ejemplo';
Ejemplo 5: Filtrar los datos al final de la cadena
Ejecute la siguiente consulta:
$ Select * de los usuarios donde el correo electrónico ~ * 'com $';
Aquí:
Filtramos las cadenas de la Correo electrónico columna basada en el extremo de la cola.
El operador "$" indica el final de la cadena.
El Regex "com $" describe que está coincidiendo con las cuerdas que tienen "com" al final.
Ejemplo 6: Filtrar los datos al comienzo de la cadena
Mira la siguiente consulta:
$ Select * de los usuarios donde el correo electrónico ~ * '^m';
Aquí:
Filtramos las cadenas de la columna de correo electrónico en función de sus iniciales.
El operador "^" indica el comienzo de una cadena.
El Regex "^m" describe que es coincidente para las cuerdas que tienen "M" al principio.
Ejemplo 7: datos de tipo numérico
También podemos usar la expresión regular para especificar las entradas de búsqueda que contienen dígitos. La siguiente consulta lo demuestra perfectamente:
$ Select * de los usuarios donde el correo electrónico ~ '[0-9]';
En esta consulta, utilizamos la característica de la clase de caracteres de la expresión regular. Básicamente, es una forma atajo de representar ciertas clases de personajes. Por ejemplo, [0-9] es una representación de la clase de caracteres de dígitos.
Como referencia, aquí hay una breve lista de varias clases de caracteres de expresión regular:
Letra mayúscula: [[:superior:]] o [ARIZONA]
Letra minúscula: [[:más bajo:]] o [Arizona]
Alfabeto: [[:alfa:]] o [A-Za-Z]
Alfanumérico: [[: Alnum:]] o [A-ZA-Z0-9]
Hexadecimal: [[: xdigit:]] o [0-9A-FA-F]
ASCII: [[: ASCII:]]
Funciones de Regex PostgreSQL
Además de la expresión regular de Posix, PostgreSQL también viene con varias funciones específicas de regex. Esta sección muestra estas funciones con ejemplos.
Regexp_replace ()
La sintaxis del Regexp_replace () La función es la siguiente:
$ Regexp_replace (, , ,)
Aquí:
fuente: Una cadena donde tiene lugar el reemplazo.
patrón: Una expresión regular de Posix que coincide con las subcontratos que deben reemplazarse.
reemplazo_string: Una cadena para reemplazar las coincidencias de patrón regex.
banderas: Es un componente opcional. Puede ser uno o más carácter que influye en el comportamiento de la función.
También podemos implementar el Regexp_replace () Para trabajar en cadenas desde entradas de base de datos:
$ Select regexp_replace (correo electrónico, '[0-9]', '*', 'g') de usuarios;
Según las instrucciones, la función reemplaza todos los dígitos de todas las entradas en la columna de correo electrónico con un asterisco (*).
Regexp_matches ()
Como sugiere el nombre de la función, Regexp_matches () se usa para encontrar todas las instancias del patrón en una cadena dada. La sintaxis de la función es la siguiente:
$ Regexp_matches (, , );
Aquí:
fuente: Una cadena donde la función busca el patrón dado.
patrón: Un patrón que se describe en la expresión regular de Posix.
banderas: Puede ser uno o más caracteres que influyen en el comportamiento de la función.
Mira el siguiente ejemplo:
$ Seleccione regexp_matches ('el zorro brown rápido', 'el', 'g');
Tenga en cuenta que Regexp_matches () Imprime la salida como un conjunto.
A continuación, filtramos los datos de nuestra base de datos:
$ Select regexp_matches (correo electrónico '.+@(.*) $ ') De los usuarios;
La salida es un conjunto de todos los dominios únicos de las direcciones de correo electrónico.
Subcadena ()
El Subcadena () la función devuelve una parte de una cadena dada. La subcadena se genera en función de los parámetros dados.
La sintaxis del Subcadena () La función es la siguiente:
$ Subcandring (, , )
Aquí:
cadena: La cadena que la función genera una subcadena desde.
Posición de salida: Un valor entero positivo que especifica la posición para extraer la subcontratación de. Si el valor es 0, la extracción de subcadena comienza en el primer carácter de la cadena.
longitud: Un valor entero positivo que especifica el número de caracteres para extraer del inicio_position. Si no se proporciona, la función actúa como si el valor fuera la longitud máxima de la cadena dada.
Los siguientes ejemplos demuestran el comportamiento de la Subcadena () función perfectamente:
$ SELECT SUBSTRING ('The Quick Brown Fox', 6, 9); $ SELECT SUBSTRING ('The Quick Brown Fox', 6);
También podemos utilizar esta función para manipular los datos que se extraen de la base de datos:
$ Seleccione Distints Subcubarning (correo electrónico desde '.+@(.*) $ ') De los usuarios;
La salida imprime todos los dominios únicos de las direcciones de correo electrónico como una lista de subcadenas.
Split_part ()
El Split_part () la función toma una cadena y la divide en diferentes partes. La sintaxis de la función es la siguiente:
$ Split_part (, , );
Aquí:
cadena: La cadena que se divide en múltiples subcadenas.
delimitador: Una cadena que se usa como delimitador para dividir.
posición: La posición de la parte para regresar. El valor comienza desde 1.
Revisar la Split_part () función en acción:
$ Select split_part ('a, b, c, d, e', ',', 4);
$ Select split_part ('a, b, c, d, e', ',', 2);
En el siguiente ejemplo, usamos el Split_part () Para dividir las direcciones de correo electrónico y tomar el dominio:
$ Select split_part (correo electrónico, '@', 2) de los usuarios;
Conclusión
En esta guía, realizamos un tutorial en profundidad sobre el uso de la expresión regular para construir las declaraciones SQL y filtrar los datos en PostgreSQL. El uso de la expresión regular permite mucha más flexibilidad que los otros métodos establecidos. Por ejemplo, usando el COMO operador.
Además de la implementación básica, también demostramos el uso de Regex en varias otras funciones PostgreSQL para mejorar en gran medida su funcionalidad. De hecho, también puede implementar la expresión regular en funciones definidas por el usuario.
Interesado en aprender más? La subcategoría PostgreSQL contiene cientos de guías y tutoriales sobre las diversas características de PostgreSQL.
