Quitar caracteres como Enter y Tabulado

Varios usuarios han reportado problemas al generar un reporte, mediante una consulta SQL, donde el resultado de cada fila lo entrega en dos o más líneas. También las columnas quedan desplazadas, apareciendo filas con más columnas que las debidas.

La mayoría de las veces este problema se debe a que un dato extraído en el reporte contiene los caracteres de control Enter y/o Tabulado.

Para evitar este problema se debe de reemplazar el o los caracteres de control por un espacio o cualquier otro carácter (puede ser incluso carácter nulo).

El Enter está formado por un salto de línea (carácter 13) y un retorno de carro (carácter 10). Para quitarlo se debe de reemplazar esta secuencia de caracteres por un espacio o cualquier texto que se desee.

El Tabulado es representado con el carácter número 9, por lo que para quitarlo se reemplaza por un espacio o el carácter deseado.

Quitar caracteres de control (Enter y Tabulado) de columnas en una consulta con Oracle:

select datos_sin_problema, replace(nombre_del_campo, chr(13)||chr(10), ' ') dato_sin_enter, replace(nombre_del_campo, chr(9), ' ') dato_sin_tabulado, replace(replace(nombre_del_campo, chr(13)||chr(10), ' '), chr(9), ' ') dato_sin_enter_ni_tabulado from nombre_de_tabla;

En esta consulta se extraen el o los datos sin problemas, y los datos con problemas se extraen en 3 formas: quitando solo el Enter, quitando solo el Tabulado y por último quitando Enter y Tabulado. Se utilizan las funciones REPLACE para sustituir los caracteres, y CHR para obtener la representación del carácter. Los caracteres se concatenan con el operador ||.

Quitar caracteres de control (Enter y Tabulado) de columnas en una consulta con Microsoft Access:

select datos_sin_problema, replace(nombre_del_campo,chr(13) + chr(10), ' ') as dato_sin_enter, replace(nombre_del_campo,chr(9), ' ') as dato_sin_tabulado, replace(replace(nombre_del_campo, chr(13) + chr(10), ' '), chr(9), ' ') as dato_sin_enter_ni_tabulado from nombre_de_tabla;

Al igual que en Oracle, esta consulta se extraen el o los datos sin problemas, y para los datos que tienen problemas se extraen en 3 formas: solo Enter, solo Tabulado y por último Entero y Tabulado.

Se utilizan las funciones REPLACE para sustituir los caracteres, y CHR para obtener la representación del carácter. La diferencia es que el texto se concatena con el operador +.

La solución presentada corrige el problema, pero es preferible evitarlo. Antes que nada se debe de analizar si el campo en cuestión debería o no almacenar estos caracteres de control. Por ejemplo si se trata de nombre del cliente no lo debería tener, pero si se trata de observaciones es válido que lo tenga.

En caso de que no sea válido, es preferible corregir el problema de raíz. Si el sistema con que se introducen los datos permite caracteres de control, se recomienda modificar dicho sistema de manera de que antes de grabar se realice la “limpieza” del texto.

Una vez controlado el ingreso de los datos, se deberá corregir los datos que ya están almacenados con caracteres de control, para esto se utiliza una simple sentencia UPDATE con las funciones antes mostradas.

Quitar caracteres de control (Enter y/o Tabulado) con Oracle:

Quitar el carácter Enter con Oracle:

update nombre_tabla set nombre_del_campo = replace(nombre_del_campo, chr(13)||chr(10), ' ') where nombre_del_campo like '%' || chr(13)||chr(10) || '%';

Quitar el carácter Tabulado con Oracle:

update nombre_tabla set nombre_del_campo = replace(nombre_del_campo, chr(9), ' ') where nombre_del_campo like '%' || chr(9) ||'%';

Quitar ambos caracteres:

update nombre_tabla set nombre_del_campo = replace(nombre_del_campo, replace(nombre_del_campo, chr(13)||chr(10), ' '), chr(9), ' ') where nombre_del_campo like '%' || chr(9) ||'%' or nombre_del_campo like '%' || chr(13)||chr(10) || '%';

Quitar caracteres de control (Enter y/o Tabulado) con Microsoft Access:

Quitar el carácter Enter con Microsoft Access:

update nombre_tabla set nombre_del_campo = replace(nombre_del_campo, chr(13) + chr(10), ' ') where nombre_del_campo like '*' + chr(13) + chr(10) + '*';

Quitar el carácter Tabulado con Microsoft Access:

update nombre_tabla set nombre_del_campo = replace(nombre_del_campo, chr(9), ' ') where nombre_del_campo like '*' + chr(9) + '*';

Quitar ambos caracteres con Microsoft Access:

update nombre_tabla set nombre_del_campo = replace(nombre_del_campo, chr(9), ' ') where nombre_del_campo like '*' + chr(9) + '*' or nombre_del_campo like '*' + chr(13) + chr(10) + '*';

Si se desea quitar algún otro carácter de control, primero se deberá de identificar de cual carácter se trata y seguir los pasos presentados.

Identificar o eliminar repetidos con Microsoft Access

Como complemento a la publicación anterior, donde se muestra como identificar registros repetidos en hojas de cálculo, se dearrollaron técnicas similares para bases de datos. Este es el turno de Microsoft Access.

Como ejemplo se utilizará una supuesta tabla "nombre_tabla", que contiene al menos los campos "campo1", "campo2" y "campo3", y se considera que un registro está duplicado cuando estos tres campos son iguales.

Esta operación no se pude realizar directamente con Microsoft Access (o se desconoce) pero si se logra realizando un par de pasos intermedios.

Primero se debe de crear un nuevo campo al que se le llamará "rownum" de tipo Autonumérico (se selecciona la tabla "nombre_tabla", se presiona Vista de Diseño, y se agrega el campo al final).

Al confirmar la operación, Microsoft Access creará la nueva columna "rownum" y la completará con números del 1 hasta la cantidad de filas que tenga la tabla.

Luego se debe de crear una consulta de actualización sobre esta tabla (dentro de las consultas, se selecciona nueva consulta en vista de diseño, se selecciona la tabla "nombre_tabla" o cualquier otra, se presiona el botón SQL, y se cambia lo que está escrito por la siguiente sentencia:

delete from nombre_tabla where nombre_tabla.rownum > ( select min(nombre_tabla_2.rownum) from nombre_tabla nombre_tabla_2 where nombre_tabla_2.campo1 = nombre_tabla.campo1 and nombre_tabla_2.campo2 = nombre_tabla.campo2 and nombre_tabla_2.campo3 = nombre_tabla.campo3);

Esta sentencia borrará los registros que para iguales campos 1 al 3 tengan el valor de rownum mayor al mínimo.

Al ejecutarla se deberá confirmar la operación (indicará la cantidad de registros borrados).

En caso de que no se desee borrar los registros duplicados, sino solamente marcarlos se deberá de crear otro campo adicional por ejemplo de tipo texto con largo 2, en este ejemplo con nombre "EsRepetido".

Al crear el campo, si no se especifica un valor predeterminado para los registros ya existentes, Microsoft Access le asigna el valor nulo. Si se desea se le puede asignar como valor predeterminado el texto No. O de lo contrario ejecutar la siguiente consulta de actualización:

update nombre_tabla set EsRepetido = 'No';

Aquí se está inicializando el atributo que indica el registro está o no repetido.

Posteriormente se ejecuta la siguiente consulta de actualización para marcar los repetidos.

update nombre_tabla set EsRepetido = 'Si' where nombre_tabla.rownum > ( select min(nombre_tabla_2.rownum) from nombre_tabla nombre_tabla_2 where nombre_tabla_2.campo1 = nombre_tabla.campo1 and nombre_tabla_2.campo2 = nombre_tabla.campo2 and nombre_tabla_2.campo3 = nombre_tabla.campo3);

Esta actualización utiliza el mismo criterio que el borrado.

En el caso que se requiera marcar los no repetidos se logra con la siguiente consulta de actualización:

update nombre_tabla set = 'No' where nombre_tabla.rownum = ( select min(nombre_tabla_2.rownum) from nombre_tabla nombre_tabla_2 where nombre_tabla_2.campo1 = nombre_tabla.campo1 and nombre_tabla_2.campo2 = nombre_tabla.campo2 and nombre_tabla_2.campo3 = nombre_tabla.campo3);

Para este caso se marcan como no repetidos aquellos registros que su "rownum" sea igual al mínimo que existe entre todos los registros que tengan mismo campos 1 al 3.

El nuevo campo "rownum" se puede eliminar o dejarlo para posteriores depuraciones.

Separar los componentes de nombres de personas

Extraer los componentes de nombres de personas no es una tarea sencilla, aquí se mostrará una manera muy básica de separar los nombres propios en sus dos apellidos y nombres.

Para este caso se parte de la premisa que los componentes del nombre están separados por espacios, e ingresados siempre en el mismo orden: apellido uno, apellido dos, y nombres.

La idea del proceso es extraer el primer apellido a partir de la primer posición del nombre completo hasta antes de la primer ocurrencia del espacio.

Para extraer el segundo apellido se parte del texto que se encuentra a partir del primer espacio hasta la ubicación del siguiente espacio.

Y por ultimo para extraer los nombres se parte desde la posición siguiente al segundo espacio (largo del primer apellido + largo del segundo apellido + 2 espacios + 1) hasta el final de la cadena de texto.

Este método tiene grandes limitantes tales como:

• No extrae apellidos o nombres compuestos (ej: DEL CAMPO).
• El orden de los componentes es fijo para todos los casos.
• Genera anomalías cuando no existen algunos de los apellidos o nombres.

Separar nombres con Microsoft Excel en español:

Partiendo de que el nombre completo se encuentra en la columna A, se dejará el primer apellido en la columna B, el segundo apellido en la columna C, y los nombres en la columna D.

Primer Apellido (B2)_

=EXTRAE(ESPACIOS(A2); 1; HALLAR(" "; ESPACIOS(A2)) - 1)

Segundo Apellido (C2):

=EXTRAE(EXTRAE(ESPACIOS(A2); LARGO(B2) + 2; LARGO(A2)); 1; HALLAR(" "; EXTRAE(ESPACIOS(A2); LARGO(B2) + 2; LARGO(A2))) - 1)

Nombres (D2):

=EXTRAE(ESPACIOS(A2); LARGO(B2) + LARGO(C2) + 3; LARGO(A2))

Separar nombres con Microsoft Excel en inglés, Google Docs, OpenOffice.org Calc, Numbres:

Se parten de las mismas premisas que en ejemplo anterior.

Primer Apellido (B2):

=MID(TRIM(A2); 1; SEARCH(" "; TRIM(A2)) - 1)

Segundo Apellido (C2):

=MID(MID(TRIM(A2); LEN(B2) + 2; LEN(A2)); 1; SEARCH(" "; MID(TRIM(A2); LEN(B2) + 2; LEN(A2))) - 1)

Nombres (D2):

=MID(TRIM(A2); LEN(B2) + LEN(C2) + 3; LEN(A2))

Separar nombres con Oracle:

Se realizará una consulta con una lógica similar a la de las hojas de cálculo, la diferencia más importante radica en que no se dispone del valor de la celda anterior para conocer su largo.

SELECT nombre_completo, substr(nombre_completo, 1, instr(nombre_completo, ' ')-1) apellido_uno, substr(substr(nombre_completo, instr(nombre_completo, ' ') + 1), 1, instr(substr(nombre_completo, instr(nombre_completo, ' ') + 1), ' ') - 1) apellido_dos, substr(nombre_completo, instr(nombre_completo, ' ', instr(nombre_completo, ' ') + 1) + 1) nombres FROM (select 'martinez gonzalez martin' nombre_completo from dual);

Esta sentencia es para prueba, cuando se disponga de una tabla cambiar el contenido del from por la tabla donde se encuentre el nombre a procesar, y modificar los nombres de campos.

Separar Nombres con Microsoft Access:

Se realizará una consulta similar a la planteada en Oracle.

SELECT nombre_completo, mid(nombre_completo, 1, instr(nombre_completo, ' ') -1) as apellido_uno, mid(mid(nombre_completo, instr(nombre_completo, ' ') + 1), 1, instr(mid(nombre_completo, instr(nombre_completo, ' ') + 1), ' ')-1) as apellido_dos, mid(nombre_completo, instr(instr(nombre_completo, ' ') + 1, nombre_completo, ' ') + 1) as nombres FROM tabla_nombres;

Tomando los datos de una tabla llamada tabla_nombres que tiene un campo nombre_completo con contiene la información a dividir.

Otras alternativas:

Vale la pena comentar que otra forma muy básica de hacer este proceso es utilizar la herramienta de Microsoft Excel de separar texto en columnas. Aparte de ser muy básica esta operación se debe de hacer por procesos y no definida en formulas como la que aquí se presenta.

Próximamente se publicarán maneras más completas de realizar estas operaciones. Soluciones con mayor inteligencia son realizadas con algoritmos más complejos basados en árboles de decisión y redes neuronales.

Aprovecho el tema para ofrecer mis servicios profesionales para la normalización de nombres propios en bases de datos a gran escala, utilizando algoritmos que interpretan los componentes sin importar el orden, formato y cantidad de componentes del mismo. Lo complementan operaciones adicionales como la identificación del sexo, e identificación nombre repetidos aun cuando estén definidos en formas diferentes (nombres completos vs iniciales, con faltas de ortografía, por sonido, etc.).

Por consultas realizarlas por este correo

Generar reportes por rangos variables

Una operación muy común al momento de recuperar datos es clasificar el resultado por rangos de valores dependiendo de un determinado criterio, ya sea para posteriormente agruparla o simplemente para saber en que categorías se encuentra.

Dentro de la Minería de Datos (datamining), podemos decir que estas operaciones están incluidas en las técnicas de Limpieza y Transformación de datos.

El proceso para identificar y agrupar los rangos es el siguiente:

1. Identificar el rango al que pertenece cada dato.

2. Agrupar por los criterios necesarios más la información del rango (paso opcional).

3. Realizar los cálculos de agregación de información necesarios (Suma, cuentas, promedios, máximos, mínimos, etc).

La forma "tradicional" de identificar el rango es hacer tantas condiciones como rangos existan, lo que lleva a un reporte complicado que depende de la cantidad de rangos, y ni hablar cuando hay que hacer cambios.

Como alternativa podemos realizar dicha operación utilizando una tabla auxiliar (lista) donde se definen los rangos, y luego mediante una misma consulta preguntar a que rango de dicha tabla pertenece cada valor.

Para cada rango se debe de realizar la siguiente pregunta:

valor_a_buscar "mayor o igual" rango_minimo Y valor_a_buscar "menor" rango_minimo

Cuando el resultado es verdadero se debe a que lo encontramos. Es muy importante no dejar huecos entre los rangos ni superponerlos para evitar perder información ni duplicarla respectivamente.

Solución para hojas de cálculo:

1. Se crea una nueva hoja, por ejemplo llamada RANGOS, donde tendremos tres columnas: A tendrá el rango mínimo, B el rango máximo, y C el nombre del rango.

2. Para buscar los rangos se debe de utilizar la BUSCARV (VLOOKUP en ingles), utilizando la característica del cuarto parámetro la cual habilita la búsqueda en rangos. Buscará el dato en la lista, de no encontrarlo nos dará el dato correspondiente al menor valor superior al que estamos buscando, aquí es muy importante tener ordenada la lista ya que de lo contrario no nos funcionará.

En la hoja donde tenemos nuestra información, se crea una nueva columna llamada Rango donde se ingresará la siguiente formula:

2.a. Formula en Microsoft Excel en Español:

=BUSCARV(B2;RANGOS!$A$2:$C$6;3;VERDADERO)

2.b. Fórmula en Google Docs, OpenOffice.org Calc, Microsoft Excel en inglés:

=VLOOKUP(B2; RANGOS.$A$2:$C$6; 3; 1)

3. En caso de necesitar agrupar la información, una vez identificado el rango, se crea una tabla dinámica que incluya esta nueva columna y se realiza el reporte. Las operaciones de agregación son provistas por la tabla dinámica (en caso de utilizar Microsoft Excel).

Hice un ejemplo con una lista de países agrupando según la información de su población y superficie, aquí queda disponible un documento de Google Docs

Para que se puedan ver las fórmulas ha quedado de libre edición, por lo que si ven algo extraño accede a la versión original (Archivo / Historial de Revisiones).

Solución para manejadores de base de datos:

1. Se crea la tabla para que contenga los rangos como mínimo con los campos que de la tabla de rangos.

2. Definir la información de los rangos ingresando los datos en la nueva tabla.

3. Al reporte (consulta) que se tenga simplemente se le debe de agregar la tabla de rangos y unirla (join) mediante:

and (valor >= rango_minimo and valor < rango_maximo)

O como un colaborador mencionó en sus comentarios:

and (valor between rango_minimo and (rango_maximo-1))

Ejemplo de reporte detallado:

select td.clave_dato, td.valor_dato, tr.nombre_rango from tabla_datos td, tabla_rangos tr where td.valor_dato >= tr.rango_minimo and td.valor_dato < tr.rango_maximo

4. En caso de requerir las agrupaciones, se deben de agrupar los datos y utilizar las operaciones de agregación necesarias.

select tr.nombre_rango, count(*) as cuenta_dato, sum(otro_valor) as suma_dato from tabla_datos td, tabla_rangos tr where td.valor_dato >= tr.rango_minimo and td.valor_dato < tr.rango_maximo group by tr.nombre_rango

Como siempre si desean mayor información o aclarar algún tema no duden en comunicarse.