En esta entrada quiero mostrar una forma para recibir una lista de correos electrónicos divididos por coma o por punto y coma y separarlos en filas independientes en una tabla, una vez desarrollado el algoritmo crearemos la función que podremos utilizar de forma muy simple en cualquier base de datos.
Para entender plenamente el ejercicio creo muy importante el explicar brevemente cómo operan las diferentes funciones para manejo de cadenas que utilizaremos en el código.
REPLACE(string, stringToFind, stringToReplace)
Esta función reemplaza todas las apariciones de una cadena en específico por la cadena que nosotros queramos. Por ejemplo, supongamos que queremos sustituir todas las letras "a" con acento por "á", el código sería el siguiente:
SELECT REPLACE('Me salieron ámpulas por subirme al árbol', 'á', 'á')
CHARINDEX(stringToFind, string[, startIndex])
Esta función busca la primera posición de izquierda a derecha en la que encuentre la cadena
stringToFind dentro de
string. El tercer parámetro opcional indica el índice a partir del cual queremos comenzar la búsqueda de la cadena
stringToFind. Por ejemplo, queremos obtener el índice de la primera ",":
SELECT CHARINDEX(',', 'Primero, segundo y tercero')
La función devolverá un valor 0 cuando no haya encontrado la cadena que se estaba buscando.
RTRIM(string) y LTRIM(string)
La función RTRIM elimina todos los espacios en blanco que encuentre a la derecha de la cadena
string, es decir, todos los espacios en blanco de relleno que pueda tener la cadena, en inglés encontramos que a esto se le llama
trailing spaces.
La función LTRIM elimina todos los espacios en blanco que encuentre al inicio de la cadena
string.
La combinación de ambas funciones RTRIM(LTRIM(string)) tiene un resultado como el de la función Trim de un objeto String de .NET
Supongamos que queremos eliminar todos los espacios a la izquierda y a la derecha de una palabra:
SELECT RTRIM(LTRIM(' con espacios antes y después '))
SUBSTRING(string, startIndex, length)
Esta función extrae
length caracteres de la cadena
string a partir del caracter
startIndex. Por ejemplo, queremos extraer la palabra "penca" de la siguiente frase:
SELECT SUBSTRING('Grabé en la penca de un maguey', 13, 5)
La palabra "penca" comienza en el caracter número 13 y tiene una longitud de 5.
Es importante notar que en SQL Server las cadenas comienzan con el índice 1, no como en .NET donde comienzan en el índice 0.
Bueno, una vez que tenemos estas funciones en mente comencemos con la programación de nuestra función.
Vamos a comenzar por declarar una variable donde se encuentren los correos electrónicos que recibiriemos como parámetro en la función:
DECLARE @Emails VARCHAR(2000) = 'email1@domain1.com, email2@domain2.com; email3@domain3.com '
Como podrás notar el primer y segundo correo están divididos por una coma mientras que el segundo y tercer correo están divididos por un punto y coma. También es importante ver que hay un espacio en blanco al final de la cadena.
Esta serie de cosas las puse así para tomar en cuenta posibles errores por parte del usuario al momento de hacerle llegar el parámetro a la función.
Vamos a cambiar los ";" por "," y así esté homogéneo el divisor de correos:
SET @Emails = REPLACE(@Emails, ';', ',')
Vamos a necesitar un par de variables, una que lleve la posición actual de análisis de la cadena de correos y otra que tenga la posición donde se encuentra la ",":
DECLARE @CurrentPos INT = 1
DECLARE @CommaPos INT = CHARINDEX(',', @Emails)
Vamos a crear una tabla temporal para ir guardando los correos, esta tabla será declarada como parte de la firma de nuestra función:
DECLARE @Result TABLE (
Email VARCHAR(255)
)
El algoritmo es sencillo, mientras siga habiendo "," hay más correos por analizar, debido a ello estableceremos un ciclo WHILE:
WHILE @CommaPos > 0
BEGIN
END
Cuando queramos extraer el correo electrónico, deberemos sacar del caracter @CurrentPos hasta el índice donde se encuentra la "," pero sin incluir la ",". También recordemos que tenemos que quitar cualquier espacio en blanco al inicio o al final del correo:
INSERT INTO @Result (Email)
VALUES (RTRIM(LTRIM(SUBSTRING(@Emails, @CurrentPos, @CommaPos - @CurrentPos))))
Debemos poner especial atención al tercer parámetro, en .NET generalmente le colocaríamos un -1 pero como en SQL Server la posición del primer caracter es 1, no es necesario realizar ese pequeño ajuste.
Vamos a actualizar nuestras variables de navegación @CurrentPos y @CommaPos:
SET @CurrentPos = @CommaPos + 1
SET @CommaPos = CHARINDEX(',', @Emails, @CurrentPos)
Notemos que en este caso estamos utilizando el tercer parámetro de la función CHARINDEX, esto se debe a que la búsqueda de la "," queremos que se realice después de la que ya habíamos encontrado.
Nuestro WHILE quedaría de la siguiente manera:
WHILE @CommaPos > 0
BEGIN
INSERT INTO @Result (Email)
VALUES (RTRIM(LTRIM(SUBSTRING(@Emails, @CurrentPos, @CommaPos - @CurrentPos))))
SET @CurrentPos = @CommaPos + 1
SET @CommaPos = CHARINDEX(',', @Emails, @CurrentPos)
END
Al salir del WHILE estaremos en condiciones de obtener el último correo con el siguiente código:
INSERT INTO @Result (Email)
VALUES (RTRIM(LTRIM(SUBSTRING(@Emails, @CurrentPos, LEN(@Emails) - @CurrentPos + 1))))
Nuestro código final debe quedar de la siguiente manera:
DECLARE @Emails VARCHAR(2000) = 'email1@domain1.com, email2@domain2.com; email3@domain3.com '
SET @Emails = REPLACE(@Emails, ';', ',')
DECLARE @CurrentPos INT = 1
DECLARE @CommaPos INT = CHARINDEX(',', @Emails)
DECLARE @Result TABLE (
Email VARCHAR(255)
)
WHILE @CommaPos > 0
BEGIN
INSERT INTO @Result (Email)
VALUES (RTRIM(LTRIM(SUBSTRING(@Emails, @CurrentPos, @CommaPos - @CurrentPos))))
SET @CurrentPos = @CommaPos + 1
SET @CommaPos = CHARINDEX(',', @Emails, @CurrentPos)
END
INSERT INTO @Result (Email)
VALUES (RTRIM(LTRIM(SUBSTRING(@Emails, @CurrentPos, LEN(@Emails) - @CurrentPos + 1))))
SELECT * FROM @Result
Una vez que lo hayamos probado y verifiquemos que funciona tal como lo esperábamos, vamos a encapsularlo en una función para poder reutilizarlo:
CREATE FUNCTION udfSplitEmails(
@Emails VARCHAR(2000)
)
RETURNS @Result TABLE(
Email VARCHAR(255)
)
AS
BEGIN
SET @Emails = REPLACE(@Emails, ';', ',')
DECLARE @CurrentPos INT = 1
DECLARE @CommaPos INT = CHARINDEX(',', @Emails)
WHILE @CommaPos > 0
BEGIN
INSERT INTO @Result (Email)
VALUES (RTRIM(LTRIM(SUBSTRING(@Emails, @CurrentPos, @CommaPos - @CurrentPos))))
SET @CurrentPos = @CommaPos + 1
SET @CommaPos = CHARINDEX(',', @Emails, @CurrentPos)
END
INSERT INTO @Result (Email)
VALUES (RTRIM(LTRIM(SUBSTRING(@Emails, @CurrentPos, LEN(@Emails) - @CurrentPos + 1))))
RETURN
END
Ahora se podrá utilizar de la siguiente manera:
SELECT * FROM udfSplitEmails('email1@domain1.com, email2@domain2.com; email3@domain3.com ')
Espero te haya resultado de utilidad esta entrada, es una función que se puede incluso integrar en la base de datos model para que forme parte del machote de una base de datos cuando sea creada en el servidor.
