DEV Community

DbVisualizer
DbVisualizer

Posted on

Clean and Compare Strings in SQL with TRIM

#sql #mysql #trim

Inconsistent string values are one of the most common causes of mismatched query results. Whether you're importing CSVs, parsing form input, or updating scraped text, you'll run into spaces, dashes, dots, or other noise. SQL's TRIM function gives you a way to clean those strings up—either on insert or before comparison.

Let’s walk through how TRIM works, some example use cases, and answers to common questions about it.

SQL TRIM in Action

You can use TRIM to remove default whitespace, or configure it to remove other characters.

1. Trim both leading and trailing whitespace:

SELECT TRIM('  Hello SQL  ');
-- Returns: 'Hello SQL'
Enter fullscreen mode Exit fullscreen mode

2. Trim just the beginning of a string:

SELECT TRIM(LEADING '-' FROM '--login');
-- Returns: 'login'
Enter fullscreen mode Exit fullscreen mode

3. Trim just the end:

SELECT TRIM(TRAILING '.' FROM 'done...');
-- Returns: 'done'
Enter fullscreen mode Exit fullscreen mode

4. Clean up form input before inserting:

INSERT INTO users (name, email)
VALUES (
  TRIM(' John Doe '),
  TRIM(' johndoe@email.com ')
);
Enter fullscreen mode Exit fullscreen mode

5. Normalize imported data:

UPDATE product_catalog
SET sku = TRIM(sku), category = TRIM(category);
Enter fullscreen mode Exit fullscreen mode

FAQ

What types of TRIM functions are there in SQL?

Standard SQL supports TRIM, which defaults to removing whitespace. You can also use LTRIM (left side) and RTRIM (right side) when needed.

Can I trim characters other than whitespace?

Yes, you can. Use this syntax:

TRIM(LEADING '0' FROM '00045') → '45'.

Is SQL Server supported?

Yes, TRIM is supported in SQL Server 2017 and later. LTRIM and RTRIM are available in earlier versions.

Does TRIM affect performance?

Only slightly. It's fine for cleanup operations or input sanitation, but avoid using it in large-scale joins or filters without indexing strategies.

Conclusion

SQL TRIM simplifies string cleanup for a range of tasks—whether it’s formatting, comparison, or removing noise from inputs. Clean strings lead to more reliable queries and data quality.

Read SQL TRIM: Removing Extra Space Characters From a String for more info.

Top comments (0)