Normalización de cadenas de texto en PL/SQL

La normalización de cadenas de texto es uno de los temas recurrentes en el desarrollo de procesos ETL. Cuando se tratan datos anagráficos, es muy común encontrarse con duplicados debidos a errores tipográficos y de data entry.

Consideremos el siguiente ejemplo:

Ejemplo Datos Duplicados

Se puede ver que los dos registros se refieren a la misma persona física, pero hay elementos que impiden considerar los registros como duplicados perfectos. El NIF ha sido transcrito mal. Es presumible que el error venga de una introducción manual de los datos ya que la N y la H están muy cerca en el teclado español. Tanto el nombre como el domicilio aparecen en dos formatos distintos, mientras que en el caso de la población es el acento el que hace que no coincidan las dos cadenas.

¿Qué herramientas tenemos en PL/SQL para matchear los dos registros y eliminar los duplicados?

La función SOUNDEX devuelve una representación fonética de una cadena. El resultado es la codificación de cómo se pronunciaría un texto en inglés. A pesar de que estemos trabajando con texto en otro idioma, la función puede resultar útil.

Si la aplicamos por ejemplo a la población, tendremos:

SELECT POBLACIO, SOUNDEX(POBLACIO)
FROM CLIENTES
WHERE NOMDES LIKE '%SCOTT%'

Ejemplo SOUNDEX

En este caso, el código resultante es el mismo y podríamos utilizarlo para considerar que tienen igual valor.

En otros casos, como el nombre, el resultado de la función SOUNDEX no nos ayuda.

SELECT NOMDES, SOUNDEX(nomdes)
FROM CLIENTES
WHERE NOMDES LIKE '%SCOTT%'

El package UTL_MATCH contiene unas funciones desarrolladas para facilitar la detección de duplicados. Hay cuatro funciones:

  • EDIT_SIMILARITY: la función calcula la distancia de Levenshtein. Esta medida debe el nombre al científico ruso que desarrolló un algoritmo para medir la distancia entre dos cadenas de texto s1 y s2. La distancia se calcula como numero de inserciones, cambios, cancelaciones de caracteres que permiten pasar de la cadena s1 a la cadena s2. La función devuelve un número que representa la distancia entre las dos cadenas: 0 indica dos cadenas idénticas. En el ejemplo, si asumimos que el formato correcto es “NOMBRE APELLIDO”, la función devuelve:
SELECT nomdes, utl_match.edit_distance(NOMDES, 'SCOTT TIGER')
FROM clientes
WHERE NOMDES LIKE '%SCOTT%'Ejemplo EDIT_DISTANCE

La distancia entre las dos cadenas no es muy grande y podríamos considerar las dos cadenas como coincidentes.

  • EDIT_DISTANCE_SIMILARITY: actúa como la función EDIT_SIMILARITY pero devuelve un valor normalizado entre 100 (cadenas coincidentes) y 0 (total discordancia).
  • JARO_WINKLER: esta función utiliza el algoritmo de Jaro-Winkler que calcula un índice de similitud entre dos cadenas, para intentar tener en cuenta posibles errores tipográficos. El valor 1 representa dos cadenas coincidentes.
    Ejemplo JARO-WINKLER
  • JARO_WINKLER_SIMILARITY: la función utiliza el algoritmo de Jaro-Winkler y normaliza los resultados entre 0 y 100.

Las funciones del paquete UTL_MATCH permiten definir un umbral de aceptabilidad para establecer si dos cadenas coinciden “lógicamente” y pueden resultar una herramienta muy útil en el proceso de data cleansing.

Evolución, innovación y transformación
37 especializaciones avaladas por Oracle
Oportunidades ilimitadas
Posts 100% Oracle
Sigue nuestro día a día