Using SQL's TRIM and REPLACE to tidy up your data

David Hunt — Fri, 06 Mar 2020 20:11:22 +0000

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:

'  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

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

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...

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:

'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 TRIMed 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

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, 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 ❤️ !!

ROW_NUMBER, PARTITION, & other reasons SQL is beautiful

David Hunt — Wed, 04 Mar 2020 21:16:28 +0000

The more I work with raw SQL, the more I enjoy it. Not only do I get a professional confidence boost, I also take back a bit of power over my data from whatever ORM happens to proliferate our codebase.

For example, we needed the lowest-priced offer per product for each marketplace.

Attempt #1

SELECT * FROM (
  SELECT ROW_NUMBER() OVER (
    PARTITION BY product_id, market ORDER BY price ASC
  ) AS r, t.* FROM offers t
) offers

WHERE offers.r = 1

That returns the best offer for each product in each marketplace. How? r refers to the "row number" of each record within its own partition i.e. grouped by product and market. Our WHERE clause allows us to only return the top offer. Results look something like:

r	product	price	market	size
1	100	45.99	amazon	c
2	100	49.00	walmart	a
1	200	15.00	walmart	d
2	200	26.50	amazon	e

Great. Next, we wanted to consider the user's size preferences.

The first solution was adding a WHERE clause to only include sizes that are in the user's preferences. But that's no good because it leaves us without any offer at all for the product when the user's size is unavailable. Instead, we can use a few cool features of SQL - WITH, arbitrarily selecting a raw value & conditional statements.

Let's create a "virtual property" in our offers selection. Let's also move it outside of our main query using WITH for readability. Then we'll sort by both our "virtual attribute" and price:

Attempt #2

WITH offers_a AS (
  SELECT *,
    CASE WHEN size IN (a, b)
      THEN 1
      ELSE 2
    END AS size_pref
  FROM offers
)

SELECT * FROM (
  SELECT ROW_NUMBER() OVER (
    PARTITION BY product_id, market ORDER BY size_pref ASC, price ASC
  ) AS r, t.* FROM offers_a t
) offers

WHERE offers.r = 1

Now, for each product, the query will return the lowest-priced offer in the user's preferred size or the lowest-priced offer in any size. Results now look something like this:

r	product	price	market	size	size_pref
1	100	49.00	walmart	a	1
2	100	45.99	amazon	c	2
1	200	15.00	walmart	d	2
2	200	26.50	amazon	e	2

The first product's offers swapped positions because size_pref takes precedence in our sorting.

We are able to feed other API endpoints with only minor alterations to our query. Primarily, we can remove the market partition wherever we load a product and its offers. That allows us to always display the most attractive, relevant price using a single query.

Feel the SQL ❤️ !!

DEV Community: David Hunt

Using SQL's TRIM and REPLACE to tidy up your data

Removing unwanted characters with TRIM()

Replacing unwanted parts of a string with REPLACE()

ROW_NUMBER, PARTITION, & other reasons SQL is beautiful