Problema:
Es bien sabido que T-SQL no tiene soporte nativo para Expresiones Regulares (RegEx) en consultas relacionadas con cadenas de texto. Por ejemplo, no se pueden buscar registros con una expresión regular en la cláusula WHERE. Sé que puedo escribir una función CLR / procedimiento almacenado con una RegEx para hacer el trabajo, pero ¿hay alguna otra forma en la que pueda consultar una tabla con expresiones regulares en código T-SQL?
Solución:
SQL Server 2016 ha integrado el lenguaje R, es decir, T-SQL puede llamar directamente a un script R y devolver el conjunto de resultados consumible directamente por el motor de base de datos de SQL Server. Con el poder del lenguaje R y su soporte nativo para RegEx, ahora podemos consultar una tabla con RegEx a través de R. Veremos algunos ejemplos para demostrar cómo usar RegEx en el procesamiento de consultas contra tablas.
Aquí hay algunos requisitos:
- Encontrar registros con números de teléfono que pueden tener diferentes formatos
- Extraer esos números de teléfono
- Eliminar palabras duplicadas que están en secuencia, como “hola hola mundo mundo” debería ser “hola mundo” después de la eliminación de duplicados.
- Combinar múltiples saltos de línea en una sola línea
Configuración del entorno de prueba:
Crearemos una tabla con algunos registros de muestra como se muestra a continuación:
use TestDB -- asumamos que esta es nuestra base de datos de prueba
go
if object_id('dbo.tblRegEx', 'U') is not null
drop table dbo.tblRegEx
create table dbo.tblRegEx (id int identity, a varchar(300), b varchar(300) );
go
insert into dbo.tblRegEx (a, b)
values ('hola hola hola mundo', 'mi número de teléfono es 321-111-1111')
, ( 'esto esto es es realmente divertido','este número (604) 991-9111 es mi teléfono celular')
, ( 'no hay duplicados aquí', 'aquí tampoco hay número de teléfono, solo mi número de licencia 111 111 2222')
, ( 'múltiples líneas en blanco
--esta es la sexta línea', '222 333-4444 es mi teléfono celular');
Esta es una tabla muy simple con 3 columnas, 2 de las cuales son de tipo de datos varchar y se utilizarán para nuestra operación de RegEx.
Código T-SQL con R:
RegEx es una herramienta poderosa pero difícil de dominar, cuando se usa correctamente, proporciona una solución elegante y eficiente. En el lenguaje R, RegEx es una característica implementada de manera madura de la que podemos aprovecharnos. Primero enumeraremos el código T-SQL y los resultados de ejecución, y luego una explicación detallada de cada sección del script.
USE TestDB
--1. Encontrar los registros que contienen un número de teléfono
exec sp_execute_external_script @language=N'R'
, @script = N'
pattern = "\\b\\(?\\d{3}\\)?[-\\s]\\d{3}-\\d{4}\\b"
outData <- subset(inData, grepl(pattern, b, perl = T))'
, @input_data_1 = N'select id, a, b from dbo.tblRegEx'
, @input_data_1_name = N'inData'
, @output_data_1_name=N'outData'
with result sets ( as object dbo.tblRegEx);
--2. Extraer el número de teléfono
exec sp_execute_external_script @language=N'R'
, @script = N'
pattern = "(\\(?\\d{3}\\)?[-\\s]\\d{3}-\\d{4}\\b)";
m <- regexpr(pattern, inData$b, perl = T)
n <- rep(NA,length(inData$b))
n[m != -1] <- regmatches(inData$b, m)
inData$c <- n;
outData <- inData;'
, @input_data_1 = N'select id, a, b from dbo.tblRegEx'
, @input_data_1_name = N'inData'
, @output_data_1_name=N'outData'
with result sets ( (id int, a varchar(300), b varchar(300), phone varchar(20)) );
-- 3. Eliminar palabras duplicadas
exec sp_execute_external_script @language=N'R'
, @script = N'
pattern <-"\\b(\\w+\\s*)(\\1\\s*)+";
inData$a <- gsub(pattern, "\\1", inData$a, perl = T );
outData <- inData;'
, @input_data_1 = N'select id, a, b from dbo.tblRegEx'
, @input_data_1_name = N'inData'
, @output_data_1_name=N'outData'
with result sets ( as object dbo.tblRegEx);
-- 4. Combinar múltiples líneas vacías en una línea
exec sp_execute_external_script @language=N'R'
, @script = N'
pattern = "(?m)(\\s*\\r?\\n){2,}";
# m <- regexpr(pattern, x , perl = T)
inData$a <- gsub(pattern, "\r\n\r\n", inData$a, perl = T );
outData <- inData;'
, @input_data_1 = N'select id, a, b from dbo.tblRegEx'
, @input_data_1_name = N'inData'
, @output_data_1_name=N'outData'
with result sets ( as object dbo.tblRegEx);
Cada instrucción T-SQL hace dos cosas:
- Lee la tabla dbo.tblRegEx y pasa el conjunto de resultados a R a través de la variable inData.
- Dentro del script R, realiza la operación de RegEx en el conjunto de datos de inData.
Explicación de RegEx:
A continuación se muestra una breve explicación de RegEx (es decir, la variable pattern) utilizada en el script:
\\b\\(?\\d{3}\\)?[-\\s]\\d{3}-\\d{4}\\b: formato de número de teléfono\\b: límite de palabra\\(?: carácter ( una vez o ninguna vez, ya que ? significa una vez o ninguna vez\\d{3}: seguido de 3 dígitos\\)?: seguido del carácter ) una vez o ninguna vez, ya que ? significa una vez o ninguna vez[-\\s]: seguido de un guión – o un espacio en blanco, es decir, \\s\\d{3}: seguido de 3 dígitos-: seguido de un guión –\\d{4}: seguido de 4 dígitos\\b: límite de palabra(\\(?\\d{3}\\)?[-\\s]\\d{3}-\\d{4}\\b): extraer el número de teléfono. Esto es casi idéntico al anterior, los paréntesis externos () son para capturar la coincidencia\\b(\\w+\\s*)(\\1\\s*)+: formato de palabras repetibles\\b: límite de palabra(\\w+\\s*): una palabra seguida de uno o más espacios(\\1\\s*): \\1 significa una copia exacta del hallazgo anterior por (\\w+\\s*)+: significa repetir una o más veces(?m)(\\s*\\r?\\n){2,}: dos o más líneas en blanco continuas(?m): establece la cadena en modo de múltiples líneas, por lo que cada línea se analiza mediante el patrón RegEx(\\s*\\r?\\n): \\s*: cero o más espacios en blanco, \\r es retorno de carro, ? significa 0 o 1 vez, \\n es salto de línea{2,}: la coincidencia anterior se repetirá una o más veces
Resultado de la consulta:
A continuación se muestra el resultado de ejecutar las cuatro declaraciones de T-SQL incrustadas en R:
- Primer resultado de datos, obtenemos exactamente 3 registros que tienen números de teléfono.
- Segundo resultado, de hecho extraemos los números de teléfono, y agregamos una columna adicional [phone] para estos números de teléfono.
- Tercer resultado, eliminamos las palabras duplicadas.
- Cuarto resultado, combinamos las múltiples líneas vacías en una sola línea, y esto no es muy claro, así que cambiaremos a “Resultados a Texto” para verlo.
Resumen:
Con la ayuda de R incrustado en SQL Server 2016, ahora podemos aprovechar las expresiones regulares para procesar algunas operaciones de consulta y manipulación de cadenas de texto. Esto evita tener que escribir objetos CLR para lograr el mismo resultado. Esto puede ser muy útil para algunos casos de negocio. En este consejo, vimos algunos ejemplos sobre cómo usar RegEx en R, y esperamos que esto te ayude a ampliar tu visión sobre cómo la integración de R en SQL Server 2016 puede ayudarnos en algunos escenarios específicos.