Uno de los pasos importantes en un proceso ETL involucra la transformación de los datos de la fuente. Esto podría involucrar la búsqueda de llaves foráneas, convertir los valores de un tipo de dato a otro, o simplemente realizar limpiezas de datos removiendo los espacios en blanco de atrás y adelante. Un aspecto de transformar los datos de la fuente que podría complicarse se refiere a remover los caracteres especiales ASCII como el caracter de nueva línea y la tabulación horizontal. En este artículo, revisamos algunos de estos problemas con los que probablemente se encontrará cuando limpie los datos de fuente que contienen caracteres ASCII especiales, y también veremos la función definida por el usuario que podría aplicarse para remover exitosamente tales caracteres.

Reemplazando los Caracteres Imprimibles ASCII

El Código Estándar Estadounidense para el Intercambio de Información (American Standard Code for Information Interchange, ASCII) es uno de los códigos numéricos estandarizados aceptados generalmente para representar datos de caracteres en una computadora. Por ejemplo, el código numérico ASCII asociado con la barra invertida (\) es 92. Muchos de los vendedores de software se adhieren a ASCII, y por lo tanto representan códigos de caracteres de acuerdo al estándar ASCII. De igual forma, SQL Server, que usa ANSI, una versión mejorada de ASCII, viene con una función CHAR integrada que puede ser usada para convertir un código numérico ASCII de vuelta a su código de carácter original (o símbolo). El Script 1 muestra un ejemplo de cómo un código numérico ASCII de 92 puede ser convertido de vuelta en un carácter de barra invertida como se muestra en la Figura 1.

El carácter de la barra invertida cae en una categoría de caracteres ASCII que es conocida como Caracteres Imprimibles ASCII, lo cual básicamente se refiere a los caracteres visibles al ojo humano. La Tabla 1 muestra un top 5 de ejemplos de Caracteres Imprimibles ASCII.

Cuando se trata de tratar problemas de calidad de datos en SQL Server, es fácil limpiar la mayor parte de los Caracteres Imprimibles ASCII simplemente aplicando la función REPLACE. Digamos por ejemplo que los datos de la fuente contienen una dirección de correo electrónico para John Doe que tiene muchos caracteres especiales inválidos, como se muestra en el Script 2.

Podríamos eliminar tales caracteres aplicando la función T-SQL REPLACE, como se muestra en el Script 3.

La ejecución del Script 3 resulta en una dirección de correo electrónico correctamente formateada, la cual es mostrada en la Figura 2.

Reemplazando Caracteres de Control ASCII

En adición a los Caracteres Imprimibles ASCII, el estándar ASCII define una lista de caracteres especiales colectivamente conocidos como Caracteres de Control ASCII. Tales caracteres típicamente no son fáciles de detectar (al ojo humano) y por tanto no son fácilmente reemplazables usando la función T-SQL REPLACE. La Tabla 2 muestra una lista de los Caracteres de Control ASCII.

Para demostrar el reto de limpiar los Caracteres de Control ASCII, he escrito una aplicación de Consolaen C#, mostrada en el Script 4 que genera un archivo de texto output.txt que contiene diferentes variaciones de la dirección de correo electrónico de John Doe (sólo la primera línea tiene la dirección de correo de John Doe en el formato correcto).

Una vista previa del archivo de texto output.txt poblado por el Script 4 es mostrado usando el programa Windows Notepad.exe en la Figura 3.

Como puede verse, parece que hay espacios en las direcciones de correo electrónico 2-4, pero es difícil decir si estos espacios son creados por el carácter de tabulación o el carácter de la barra espaciadora. Además, si vuelve al Script 4, usted recordará que para la 3ª dirección de correo electrónico incluí el carácter inicio de cabecera al final de la dirección de correo electrónico, pero viendo los datos en la Figura 3, el carácter inicio de cabecera no es fácilmente visible al final de esa 3ª dirección de correo electrónico. De hecho, parece que las direcciones de correo electrónico 3 y 4 tienen la misma cantidad de caracteres, lo cual no es cierto. Sólo usando editores de texto avanzados como Notepad++ somos capaces de visualizar los caracteres especiales en los datos, como se muestra en la Figura 4.

Cuando se trata de SQL Server, la limpieza y remoción de los Caracteres de Control ASCII son un poco complicadas. Por ejemplo, digamos que hemos importado datos exitosamente desde el archivo de texto output.txt a la tabla de la base de datos SQL Server. Si fuéramos a ejecutar la función T-SQL REPLACE contra los datos como hicimos en el Script 3, ya podemos ver en la Figura 5 que la función REPLACE fue fallida, ya que el tamaño de los datos en la columna original es exactamente similar al tamaño calculado después de haber aplicado las funciones REPLACE y TRIM.

¿Así que, cómo reemplazamos lo que no podemos ver?

1. Reemplazar la Cadena usando Códigos de Caracteres

   La manera más fácil de reemplazar lo que no podemos ver es que, en lugar de codificar la cadena a reemplazar en nuestra función REPLACE, deberíamos codificar la cadena a ser reemplazara codificando su código numérico ASCII dentro de la función CHAR. Por tanto, en lugar de proveer un signo de admiración como la cadena a reemplazar, podemos codificar el código numérico ASCII para el signo de admiración, el cual es 33 y convierte ese código numérico en el código de carácter usando la función CHAR. Por tanto, nuestro script cambia de:

   1 2	DECLARE @email VARCHAR(55)= 'johndoe@a!bc.com'; SELECT REPLACE(@email, '!', '');

   A usar:

   1 2	DECLARE @email VARCHAR(55)= 'johndoe@a!bc.com'; SELECT REPLACE(@email, CHAR(33), '');

   Script 6

   Ahora, volviendo a la limpieza de los datos de dirección de correo electrónico en el archivo de texto output.txt, podemos rescribir nuestro script a lo que es mostrado en el Script 7.

   1 2 3 4 5

   SELECT [id],        [Column 0],        LEN([Column 0]) OriginalLength,        LEN(REPLACE(REPLACE([Column 0], CHAR(1), ''), CHAR(9), '')) NewLength FROM [SQLShack].[dbo].[OLE DB Destination];

   Script 7

   Después de ejecutar el Script 7, podemos ver en la Figura 6 que el tamaño de todas las filas de dirección de correo electrónico coincide con el tamaño de la fila 1, la cual era originalmente la dirección correcta de correo electrónico. Por tanto, hemos logrado exitosamente remover los caracteres especiales “invisibles”.

   

   Figura 6

2. Detectar y Reemplazar Dinámicamente Caracteres ASCII

   Una limitación notable del Script 7 es que hemos codificado la lista de valores numéricos ASCII. Esto significa que si los datos de direcciones de correo electrónico contienen caracteres especiales con el valor numérico ASCII 8, entonces no los habríamos removido, ya que hemos codificado nuestro script para buscar específicamente CHAR(1) y CHAR(9). Por lo tanto, hay una necesidad de un mecanismo que nos permita detectar automáticamente los Caracteres de Control ASCII contenidos en una cadena dada, y luego reemplazarlos automáticamente. El Script 8 provee tal mecanismo en una forma de bucle While dentro de una función definida por el usuario que busca iterativamente a través de una cadena dada para identificar y reemplazar Caracteres de Control ASCII.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

   CREATE FUNCTION [dbo].[ReplaceASCII](@inputString VARCHAR(8000)) RETURNS VARCHAR(55) AS      BEGIN          DECLARE @badStrings VARCHAR(100);          DECLARE @increment INT= 1;          WHILE @increment <= DATALENGTH(@inputString)              BEGIN                  IF(ASCII(SUBSTRING(@inputString, @increment, 1)) < 33)                      BEGIN                          SET @badStrings = CHAR(ASCII(SUBSTRING(@inputString, @increment, 1)));                          SET @inputString = REPLACE(@inputString, @badStrings, '');                  END;                  SET @increment = @increment + 1;              END;          RETURN @inputString;      END; GO

   Script 8

   La aplicación de la función es mostrada en el Script 9.

   1 2 3 4 5

   SELECT [id],        [Column 0],        LEN([Column 0]) OriginalLength,        LEN([SQLShack].[dbo].[ReplaceASCII]([Column 0])) NewLength FROM [SQLShack].[dbo].[OLE DB Destination];

   Script 9

Conclusión

De vez en cuando, los desarrolladores de T-SQL se enfrentan con la limpieza de datos que han importado usualmente aplicando la función T-SQL REPLACE. De todos modos, cuando se trata de remover caracteres especiales, la remoción de Caracteres de Control ASCII puede ser complicada y frustrante. Afortunadamente, SQL Server viene con las funciones adicionales integradas como CHAR y ASCII, que pueden ayudar a detectar y reemplazar automáticamente Caracteres de Control ASCII.

Vea más

Considere estas herramientas gratis para SQL Server que mejoran la productividad del desarrollador de bases de datos.

Referencias

• Función ASCII
• Diferencia entre ANSI y ASCII
• Función CHAR

• Acerca de
• Últimas entradas

Sifiso Ndlovu

Sifiso es un profesional basado en Johannesburgo dentro de un amplio rango de Competencias en Tecnología Microsoft como SQL Server y Visual Studio Application Lifecycle Management.
Él es un miembro del Grupo de Usuarios de SQL de Johannesburgo y tiene un grado de maestría en MCom IT Management de la Universidad de Johannesburgo.
Actualmente, él trabaja para Sambe Consulting como Consultor principal.

Ver todas las publicaciones de Sifiso W. Ndlovu

Últimas entradas de Sifiso Ndlovu (ver todo)

• Entendiendo el Impacto de las sugerencias NOLOCK y WITH NOLOCK en SQL Server - May 28, 2018
• Cómo reemplazar caracteres especiales ASCII en SQL Server - May 25, 2018
• Convertir resultados de SQL Server a JSON - June 2, 2017