Curso de Cleaning Data con SQL server

Autor: Luis Fernando Apáez

• Comparing the similarity between strings

  • Función SOUNDEX()
  • Función DIFFERENCE()

---

Comparing the similarity between strings

En esta clase compararemos la similitud entre cadenas, y para ello utilizaremos las funciones SOUNDEX() Y DIFFERENCE().

Por ejemplo, consierando la tabla prueba.dbo.airport podemos observar que

SELECT * FROM prueba.dbo.airports WHERE airport_state LIKE 'Il%';

(2 rows affected)

Total execution time: 00:00:00.415

airport_code	airport_name	airport_city	airport_state
ORD	Chicago O'Hare International	ch	Illinois
MDW	Chicago Midway International	Chicago	Ilynois

en la columna airport_state tenemos dos nombres muy similares para el estado de Illinois, siendo incorrecto escribir Ilynois lo cual seguramente proviene de un error de dedo al ingresar los datos. Con las funciones que mencionamos al inicio, SQL server proporciona ayuda para detectar estas cadenas.

Función SOUNDEX()

Esta función corresponde a un algorítmo fonético el cual devuelve un código de 4 caracteres y evalúa la similitud entre cadenas según su pronunciación. Por ejemplo, consideremos

SELECT SOUNDEX('Illinois') AS soundex_code
UNION ALL
SELECT SOUNDEX('Ilynois') 
UNION ALL
SELECT SOUNDEX('California')

(3 rows affected)

Total execution time: 00:00:00.101

soundex_code
I452
I452
C416

de donde el código soundex para las palabras Illinois e Ilynois es el mismo, mientras que el código soundex para la palabra California es distinto. Que las primeras dos cadenas hayan obtenido el mismo código soundex indica que éstas son muy similares (bajo este algorítmo). Cabe resaltar que siempre debemos corroborar los resultados que nos arroje soundex, pues a pesar de ser muy útil no siempre arroja la similitud entre dos cadenas. Por ejemplo, las palabras Arizona y Arkansas tienen el mismo código soundex a pesar de no ser similares

SELECT SOUNDEX('Arizona') AS soundex_code
UNION ALL
SELECT SOUNDEX('Arkansas')

(2 rows affected)

Total execution time: 00:00:00.109

soundex_code
A625
A625

Luego, podemos escribir el siguiente código el cual muestra todos los valores de la columna airport_state que tienen el mismo código soundex pero que están escritos de manera diferente (como en el caso de Arizona y Arkansas):

SELECT DISTINCT A1.airport_state 
FROM prueba.dbo.airports AS A1
INNER JOIN prueba.dbo.airports AS A2
ON SOUNDEX(A1.airport_state) = SOUNDEX(A2.airport_state)
AND A1.airport_state <> A2.airport_state;

(9 rows affected)

Total execution time: 00:00:00.862

airport_state
Caalifornia
California
Californiaa
Illinois
Ilynois
New Jersey
New York
Tejas
Texas

lo cual nos arroja en las primeras filas las distintas formas en que se ingresó la cadena California, después las distintas formas en que se ingreso la cadena Illinois, en las últimas dos filas las distintas formas en que se ingresó la cadena Texas. Notamos un problema en las filas 6 y 7 pues soundex arroja que las palabras New York y New Jersey son similares. Este problema no es como el caso de las cadenas Arizona y Arkansas, pues en realidad soundex está comparando sólo la cadena hasta antes del espacio en blanco, es decir, se está comparando la cadena New con New en vez de New York y New Jersey. Para resolver este problema podemos utilizar la función REPLACE() para omitir los espacios en blanco entre palabras:

SELECT DISTINCT A1.airport_state 
FROM prueba.dbo.airports AS A1
INNER JOIN prueba.dbo.airports AS A2
ON SOUNDEX(REPLACE(A1.airport_state, ' ', '')) = SOUNDEX(REPLACE(A2.airport_state, ' ', ''))
AND A1.airport_state <> A2.airport_state;

(7 rows affected)

Total execution time: 00:00:00.424

airport_state
Caalifornia
California
Californiaa
Illinois
Ilynois
Tejas
Texas

con lo cual vemos que el código anterior ya sólo nos arroja aquellas cadenas que son similares, pues ahora soundex no encontró coincidencias entre New York y New Jersey.

Función DIFFERENCE()

Esta función compara dos valores soundex y devuelve un número entero entre el 0 y el 4, donde el número 0 indica poca o nula similitud y el número 4 indica una coincidencia muy similar o idéntica. La sintaxis básica de la función DIFFERENCE() es

DIFFERENCE(character_expression1, character_expression2)

Ahora, por ejemplo veamos que

SELECT DIFFERENCE('Illinois', 'Ilynois') AS dif;

(1 row affected)

Total execution time: 00:00:00.173

dif
4

lo cual nos estaría indicando que las cadenas Illinois e Ilynois son muy similares. De forma análoga a como lo hicimos en el ejemplo del código usando sundex, podemos verificar similitudes entre estados (airport_state) que tienen una diferencia igual a 4 (es decir que son muy similares) pero que están escritos de manera diferente

SELECT DISTINCT A1.airport_state 
FROM prueba.dbo.airports AS A1
INNER JOIN prueba.dbo.airports AS A2
ON DIFFERENCE(REPLACE(A1.airport_state, ' ', ''), REPLACE(A2.airport_state, ' ', '')) = 4
AND A1.airport_state <> A2.airport_state;

(8 rows affected)

Total execution time: 00:00:02.522

airport_state
Caalifornia
California
Californiaa
Illinois
Ilynois
Massachusetts
Tejas
Texas

De nuevo, así como con la función soundex, es preciso revisar los resultados obtenidos, dado que la función difference puede llegar a tener discrepancias, como es el caso de la cadena Massachusetts.