How to trim strings in SQL

Learn how to trim strings in SQL with examples and explanations from SQLPad.

Problem

You need to remove leading and/or trailing spaces from a string in SQL.

Sample Data

Let's assume we have a table called employees with the following data:

id	first_name	last_name
1	' John '	' Doe '
2	' Jane '	' Doe '
3	' Jim '	' Beam '

In this table, both the first_name and last_name fields have leading and trailing spaces which we need to remove.

MySQL Solution

SELECT id, TRIM(first_name) AS trimmed_first_name, TRIM(last_name) AS trimmed_last_name FROM employees;

Explanation of MySQL Solution

In MySQL, the TRIM() function is used to remove unwanted leading and/or trailing spaces from a string. In the given solution, we are using TRIM() to remove spaces from the first_name and last_name fields. The AS keyword is used to rename the output column names for readability.

PostgreSQL Solution

SELECT id, TRIM(first_name) AS trimmed_first_name, TRIM(last_name) AS trimmed_last_name FROM employees;

Explanation of PostgreSQL Solution

In PostgreSQL, similar to MySQL, the TRIM() function is used to remove leading and/or trailing spaces from a string. The given solution is identical to the MySQL solution as the TRIM() function works in the same way in both SQL databases.