How do I clean text, numbers and blanks in SQL? TRIM, CASE, TRY_CAST and COALESCE
Repeat the city, amount and discount cleaning in DuckDB, and find out that SQL also leaves blanks without warning.
Free course. Your Maestro, an AI, .
Often the data doesn't arrive in a file but in a database, and the cleaning happens in SQL before anyone opens pandas. The good news: the method is the same. Look, decide, fix in code, count and write it down. Only the words change.
In this module you work with DuckDB, a database that runs right here, on the same March sales sheet: a ventas table with the same 60 rows. This course assumes you can already write a SELECT with WHERE and GROUP BY; what's new are the functions that clean. Here's how what you did in pandas translates:
| In pandas | In SQL (DuckDB) | What for |
|---|---|---|
str.strip() | TRIM(ciudad) | Remove spaces at the start and the end |
str.lower() | LOWER(ciudad) | Lowercase |
map(CIUDADES) | CASE ... WHEN ... THEN ... END | Send each form to its official name |
str.replace("$", "") | REPLACE(monto, '$', '') | Remove characters |
pd.to_numeric(..., errors="coerce") | TRY_CAST(... AS DECIMAL(10, 2)) | Convert without stopping |
fillna(0) | COALESCE(descuento, 0) | Fill a blank with a value |
Open the steps one by one. We ran every query on the course table.
The March sheet, cleaned in one query
Worked example · 5 steps
You want a query that returns each sale with the official city, the amount as a number and the discount with no blanks, without changing the original table.
Work out the next step yourself, then open it to compare.
Two resemblances to pandas are no accident. A CASE without an ELSE behaves like map(): whatever it doesn't know becomes NULL. And TRY_CAST behaves like errors="coerce": whatever it can't convert becomes NULL, and it carries on without a word. In both languages, cleaning that never stops is convenient, and it means you have to count.
One practical difference: in SQL you don't modify the table, you write a query that reads it and returns the clean version. It's the same idea as in pandas, where the raw file isn't touched and the code produces the clean one.
At your job, do you clean in the database or afterward, in Excel or pandas? Tell your Maestro how your data gets from where it's stored to your report.
Try it
The query below counts sales by official city, but its CASE is missing two forms. Run it as is and look at the last row. If you don't know which forms are missing, ask your Maestro for a hint.
Run the query and look at the row with the empty city. Find which ways of writing the city aren't in the CASE (you can add a query that counts LOWER(TRIM(ciudad))) and add them with their official names. You're done when the result has exactly 4 cities, no empty row, and adds up
Your turn
Run the query and look at the row with the empty city. Find which ways of writing the city aren't in the CASE (you can add a query that counts LOWER(TRIM(ciudad))) and add them with their official names. You're done when the result has exactly 4 cities, no empty row, and adds up to 60 sales.
Tables: ventas (captura, folio, fecha, cliente, ciudad, producto, kilos, monto, descuento)
ventas · 60 rows
| Column | Type |
|---|---|
| captura Orden en que se capturó el renglón en la hoja (1 es el primero). | BIGINT |
| folio Folio de la venta. | VARCHAR |
| fecha Fecha de la venta, AAAA-MM-DD. | VARCHAR |
| cliente | VARCHAR |
| ciudad Ciudad del cliente, como se capturó. | VARCHAR |
| producto | VARCHAR |
| kilos Kilos vendidos, como se capturaron. | DOUBLE |
| monto Monto de la venta en pesos, como texto tal como se capturó. | VARCHAR |
| descuento Descuento en porcentaje; vacío cuando no hubo descuento. | DOUBLE |
First rows
| captura | folio | fecha | cliente | ciudad | producto | kilos | monto | descuento |
|---|---|---|---|---|---|---|---|---|
| 1 | V-0001 | 2026-03-01 | Abarrotes Doña Mary | Guadalajara | Café tostado | 28 | $7,784.00 | 10 |
| 2 | V-0002 | 2026-03-01 | Grano Fino | Ciudad de México | Café verde | 46 | 4,692 | 10 |
| 3 | V-0003 | 2026-03-02 | Hotel Mirador | puebla | Café tostado | 45 | 14,130 | NULL |
Every run starts from fresh copies of the course's tables, so try anything. Tab indents; to leave the editor, press Escape, then Tab.
Ready to run your query.
With the incomplete CASE you get Ciudad de México 28, Guadalajara 8, Oaxaca de Juárez 9, Puebla 13 and an empty row with 2 sales: the "gdl" and the "oaxaca". With the complete one, there are 4 cities: Ciudad de México 28, Guadalajara 9, Oaxaca de Juárez 10 and Puebla 13. The empty row is your blank count: in SQL it shows up on its own, because GROUP BY puts the NULLs in a group of their own.
Now the amount. Find the sales whose amount is empty or couldn't be converted, as you did in pandas. If TRY_CAST returns more rows than you expect, ask your Maestro which character you still need to remove.
Write a query that returns folio and monto for the sales where the amount, with the peso sign and commas removed and converted with TRYCAST(... AS DECIMAL(10, 2)), comes out NULL. Order by folio. You're done when you get exactly the two sales you already know from module 2.
Your turn
Write a query that returns folio and monto for the sales where the amount, with the peso sign and commas removed and converted with TRY_CAST(... AS DECIMAL(10, 2)), comes out NULL. Order by folio. You're done when you get exactly the two sales you already know from module 2.
Tables: ventas (captura, folio, fecha, cliente, ciudad, producto, kilos, monto, descuento)
ventas · 60 rows
| Column | Type |
|---|---|
| captura Orden en que se capturó el renglón en la hoja (1 es el primero). | BIGINT |
| folio Folio de la venta. | VARCHAR |
| fecha Fecha de la venta, AAAA-MM-DD. | VARCHAR |
| cliente | VARCHAR |
| ciudad Ciudad del cliente, como se capturó. | VARCHAR |
| producto | VARCHAR |
| kilos Kilos vendidos, como se capturaron. | DOUBLE |
| monto Monto de la venta en pesos, como texto tal como se capturó. | VARCHAR |
| descuento Descuento en porcentaje; vacío cuando no hubo descuento. | DOUBLE |
First rows
| captura | folio | fecha | cliente | ciudad | producto | kilos | monto | descuento |
|---|---|---|---|---|---|---|---|---|
| 1 | V-0001 | 2026-03-01 | Abarrotes Doña Mary | Guadalajara | Café tostado | 28 | $7,784.00 | 10 |
| 2 | V-0002 | 2026-03-01 | Grano Fino | Ciudad de México | Café verde | 46 | 4,692 | 10 |
| 3 | V-0003 | 2026-03-02 | Hotel Mirador | puebla | Café tostado | 45 | 14,130 | NULL |
Every run starts from fresh copies of the course's tables, so try anything. Tab indents; to leave the editor, press Escape, then Tab.
Ready to run your query.
You get V-0036, with the empty amount, and V-0050, with "pendiente": the same two you found in pandas. If you got many more, you probably converted without removing the peso sign or the commas: TRY_CAST doesn't stop, it just returns NULL for every amount that has a "$" or a ",", and in this sheet that leaves 47 of 60 amounts as NULL. It's the same silent-conversion trap, now in SQL.