Curso de Cleaning Data con SQL server

Autor: Luis Fernando Apáez

• Cleaning messy data

  • función REPLACE

---

Cleaning messy strings

En esta clase trabajaremos con cadenas de texto que no tienen el formato que queremos.

Veamos por ejemplo, la siguiente tabla:

SELECT * FROM prueba.dbo.carriers;

(16 rows affected)

Total execution time: 00:00:01.533

code	name
YV	Mesa Airlines Inc.
AA	American Airlines Inc .
B6	JetBlue Airways
DL	Delta Air Lines Inc.
HA	Hawaiian Airlines Inc.
MQ	American Eagle Airlines Inc.
EV	ExpressJet Airlines Inc.
UA	United Air Lines Inc.
US	US Airways Inc.
VX	Virgin America
FL	AirTran Airways Corporation
OO	SkyWest Airlines Inc.
F9	Frontier Airlines Inc.
WN	Southwest Airlines Co.
AS	Alaska Airlines Inc.
NK	Spirit Air Lines

observamos en la columna name que algunos nombres tienen espacios en blanco al inicio y al final, así como más de un espacio en blanco al inicio del nombre.

Procedemos a eliminar estos espacios en blanco valiéndonos de la función TRIM():

TRIM([characters] string)

el cual elimina cualquier carácter especificado de una cadena de texto, lo cual es justo lo que buscamos. Si no especificamos un carácter, la función eliminará por default elcarácter correspondiente a espacios en blanco.

Por ejemplo

SELECT TRIM('  Jetblue Airways  ');

(1 row affected)

Total execution time: 00:00:00.060

(No column name)
Jetblue Airways

notamos que los espacios en blanco del inicio y el final se han eliminado. De tal forma, si deseamos que todos los nombres de la columna name estén sin espacios en blanco al inicio y al final, ejecutamos

SELECT code, TRIM(name) AS name FROM prueba.dbo.carriers;

(16 rows affected)

Total execution time: 00:00:00.348

code	name
YV	Mesa Airlines Inc.
AA	American Airlines Inc .
B6	JetBlue Airways
DL	Delta Air Lines Inc.
HA	Hawaiian Airlines Inc.
MQ	American Eagle Airlines Inc.
EV	ExpressJet Airlines Inc.
UA	United Air Lines Inc.
US	US Airways Inc.
VX	Virgin America
FL	AirTran Airways Corporation
OO	SkyWest Airlines Inc.
F9	Frontier Airlines Inc.
WN	Southwest Airlines Co.
AS	Alaska Airlines Inc.
NK	Spirit Air Lines

Función REPLACE()

Observemos que en

SELECT TOP 5 * FROM prueba.dbo.airports WHERE airport_state LIKE 'fl%';

(4 rows affected)

Total execution time: 00:00:00.358

airport_code	airport_name	airport_city	airport_state
MIA	Miami International	Miami	fl
MCO	Orlando International	Orlando	Florida
TPA	Tampa International	Tampa	Fl
FLL	Fort Lauderdale-Hollywood International	Fort Lauderdale	FL

en la columna airport_state hay 3 formas de referirse al estado de Florida: fl, FL y Florida. Supongamos que deseamos unificar estas tres formas a una sola, en la cual es de nuestro interés considerar sólo el nombre de Florida. PAra ello utilizaremos la función REPLACE():

REPLACE(string_to_replace, ocurrences, string_replacement)

que reemplaza todas las apariciones de una cadena especificada con otra cadena. El reemplazo no distingue entre mayúsculas y minúsculas de forma predeterminada.

Así, comenzamos por reemplazar la cadena FL por Florida

SELECT 
    airport_code, airport_name, airport_city,
    REPLACE(airport_state, 'FL', 'Florida') AS airport_state
FROM prueba.dbo.airports
WHERE airport_state LIKE 'fl%';

(4 rows affected)

Total execution time: 00:00:00.366

airport_code	airport_name	airport_city	airport_state
MIA	Miami International	Miami	Florida
MCO	Orlando International	Orlando	Floridaorida
TPA	Tampa International	Tampa	Florida
FLL	Fort Lauderdale-Hollywood International	Fort Lauderdale	Florida

efectuando la misma consulta que antes, ya no obtenemos los nombre fl o FL para el estado de florida. Además, recordemos que no es necesario ejecutar la misma consulta para

REPLACE(airport_state, 'fl', 'Florida')

pues REPLACE() no distingue entre mayúsculas y minúsculas. Notamos también la existencia de un registro cuyo nombre asignado en airport_state es Floridaorida, pues, en aquellas palabras Florida que teníamos antes de aplicar REPLACE() se sustituyó Fl por Florida, de modo que obtuvimos

Fl ---> Florida
(Fl)orida ---> (Florida)orida

Para solucionar este problema podemos anidar la función REPLACE() como sigue

SELECT 
    airport_code, airport_name, airport_city,
    REPLACE(
        REPLACE(airport_state, 'FL', 'Florida'),
        'Floridaorida', 'Florida'
    ) AS airport_state
FROM prueba.dbo.airports
WHERE airport_state LIKE 'fl%';

(4 rows affected)

Total execution time: 00:00:00.337

airport_code	airport_name	airport_city	airport_state
MIA	Miami International	Miami	Florida
MCO	Orlando International	Orlando	Florida
TPA	Tampa International	Tampa	Florida
FLL	Fort Lauderdale-Hollywood International	Fort Lauderdale	Florida

donde ahora reemplazamos todas las cadenas Floridaorida por Florida.

Una alternativa al código anterior es mediante las sentencias CASE-WHEN:

SELECT 
    airport_code, airport_name, airport_city,
    CASE
        WHEN airport_state <> 'Florida'
        THEN REPLACE(airport_state, 'FL', 'Florida')
        ELSE airport_state
    END AS airport_state
FROM prueba.dbo.airports
WHERE airport_state LIKE 'fl%';

(4 rows affected)

Total execution time: 00:00:01.002

airport_code	airport_name	airport_city	airport_state
MIA	Miami International	Miami	Florida
MCO	Orlando International	Orlando	Florida
TPA	Tampa International	Tampa	Florida
FLL	Fort Lauderdale-Hollywood International	Fort Lauderdale	Florida

donde remplazamos FL por Florida en todos aquellos registros que no tengan previamente la palabra Florida, y en aquellos registros que no tengan FL dejaremos tal cual su registro. Esto último nos sirve para dejar intactos los registros que ya tienen la palabra Florida.

Otro ejemplo es si intentamos unificar la forma de referirnos a Florida como FL. Un primer intento:

SELECT 
    airport_code, airport_name, airport_city,
    REPLACE(airport_state, 'Florida', 'FL')
    AS airport_state
FROM prueba.dbo.airports
WHERE airport_state LIKE 'fl%';

(4 rows affected)

Total execution time: 00:00:00.412

airport_code	airport_name	airport_city	airport_state
MIA	Miami International	Miami	fl
MCO	Orlando International	Orlando	FL
TPA	Tampa International	Tampa	Fl
FLL	Fort Lauderdale-Hollywood International	Fort Lauderdale	FL

pero notamos que la forma fl continúa en los registros, es decir, lo anterior no soluciona del todo el problema. No obstante, nos podemos valer de la función UPPER():

SELECT 
    airport_code, airport_name, airport_city,
    UPPER(
        REPLACE(airport_state, 'Florida', 'FL')
    ) AS airport_state
FROM prueba.dbo.airports
WHERE airport_state LIKE 'fl%';

(4 rows affected)

Total execution time: 00:00:02.048

airport_code	airport_name	airport_city	airport_state
MIA	Miami International	Miami	FL
MCO	Orlando International	Orlando	FL
TPA	Tampa International	Tampa	FL
FLL	Fort Lauderdale-Hollywood International	Fort Lauderdale	FL

donde convertimos todos los fl a FL mediante la función UPPER() (la cual convierte cadenas de letras minúsculas a cadenas de letras mayúsculas).