Handling Hidden Spaces: TRIM, LTRIM, RTRIM

Welcome to **Day 78**. Today we're fixing the most invisible bug in SQL: **Whitespace**.

The Problem

A user signs up and accidentally hits the spacebar at the end of their username: `"bob "`.

Later, they try to log in as `"bob"`.

To the database, these are NOT the same. Your query `WHERE username = 'bob'` will return nothing, and the user will get a "Password Incorrect" error.

The Solution: TRIM()

`TRIM()` removes spaces from both the beginning and the end of a string.

SELECT * FROM users

WHERE TRIM(username) = 'bob';

Variations

Pro Tip: Indexing

Be careful! Using `TRIM(column)` in your `WHERE` clause can prevent the database from using an index on that column. It's better to `TRIM` the data **before** it gets saved to the database.

If it's already messy, you might need a **Function-Based Index**.

Your Task for Today

Run `SELECT username FROM users WHERE username LIKE '% %'` to see if anyone has accidental spaces in their names.

*Day 79: CAST vs TO_DATE—Fixing String Dates.*