Using SQL's TRIM and REPLACE to tidy up your data
Tags: sql
Published: 2020-03-06 20:11:22

---
title: Using SQL's TRIM and REPLACE to tidy up your data
published: true
cover_image: https://images.unsplash.com/photo-1515705576963-95cad62945b6?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=1950&q=80
description: Running raw SQL queries to normalize your data.
series: Love Letters to SQL
tags: SQL
---

Have you ever come across a pesky bug caused by leading, trailing, or otherwise unwanted spaces? Perhaps a lowercase "l" masquerading as a big "I". How long did you sit staring at the expected output and the corrupted value before you noticed the intruder? Probably too long.

Don't worry, we can sulk together.

The better question is, how did you fix it? You likely wrote tests and merged a fix into your ETL pipeline. But there are always straggler records in need of the almighty and powerful raw SQL query.

I present to you two such cases along with viable solutions.

# Removing unwanted characters with TRIM()
> The TRIM() function removes the space character OR other specified characters from the start or end of a string.

This function should look familiar to experienced programmers. You will find it built-in to most languages; in JavaScript, for example:
```js
'  Am I in the front?'.trim()
// => 'Am I in the front?'
```

It works mostly the same way in SQL. Assume we have ingested the following data

| id| product | price | market | size |
|---|---------|-------|--------|------|
| 1 |    100  | 45.99 | amazon |  a   |
| 2 |    100  | 49.00 | walmart|  a   |
| 3 |    200  | 15.00 | walmart|  a   |
| 4 |    200  | 26.50 | amazon |  a   |

While it all looks above aboard, some records aren't being returned when we think they should
```sql
SELECT * FROM mytable WHERE size = 'a'
```
only returns

| id| product | price | market | size |
|---|---------|-------|--------|------|
| 1 |    100  | 45.99 | amazon |  a   |
| 4 |    200  | 26.50 | amazon |  a   |

Weird. So we try a `LIKE` search
```sql
SELECT * FROM mytable WHERE size LIKE '%a%'
```
We get back all four results!

> To save time, let's assume you print and/or inspect the suspicious records using your favorite GUI and discover that the actual value of size for those records is `' a'` instead of `'a'`. Let's also assume you debugged your ETL pipeline and shipped a fix.

Now, let's update the corrupted data...
```sql
UPDATE mytable

SET size = TRIM(size)

WHERE size LIKE '%a%'
```
Boom! Our corrupted data is so fresh and so clean now.

# Replacing unwanted parts of a string with REPLACE()
> The REPLACE() function replaces all occurrences of a substring within a string, with a new substring.

Hello, old friend.

Replace is another familiar function. In JavaScript, replace looks like this:
```js
'My name is Bob'.replace('Bob', 'Dave')
// => 'My name is Dave'
```

We have a few shoes for sale and for some reason, one of our suppliers likes to add decimals to whole numbers:

| id|  shoe   | price | market | size |
|---|---------|-------|--------|------|
| 1 |    100  | 45.99 | amazon |  10  |
| 2 |    100  | 49.00 | walmart| 10.0 |
| 3 |    200  | 15.00 | walmart| 11.5 |
| 4 |    200  | 26.50 | amazon |  11  |

This isn't good, and like the problem we `TRIM`ed away, this makes it difficult to query our data correctly. First, we go and fix our ETL pipeline. Next, we come back to SQL for additional cleanup.

_We could totally use a feature of `TRIM` to fix our latest issue, but we're learning and using `REPLACE` feels more familiar._

Our new query goes like this
```sql
UPDATE mytable

SET size = REPLACE(size, '.0', '')

WHERE size LIKE '%.0'
```
Take a look at our data, now, it cleans up so well:

| id|  shoe   | price | market | size |
|---|---------|-------|--------|------|
| 1 |    100  | 45.99 | amazon |  10  |
| 2 |    100  | 49.00 | walmart|  10  |
| 3 |    200  | 15.00 | walmart| 11.5 |
| 4 |    200  | 26.50 | amazon |  11  |

---

Woo, hopefully you learned something neat about SQL. Like I said in [my previous post](https://dev.to/david/rownumber-partition-other-reasons-sql-is-beautiful-1ji), using raw SQL gives me a boost of confidence; the more I do it, the more I enjoy it.

Do you have any memorable cases solved by an interesting SQL query? I'd love to hear about it below!

Feel the SQL ❤️ !!