How to Trim Strings in SQL

Problem:

You’d like to remove specific characters from the beginning and end of a string in SQL.

Example:

Our database has a table named post with data in two columns: id and title.

Let’s trim the title of each post to remove the unnecessary space at the beginning and end.

Solution:

We’ll use the TRIM function. Here’s the query you would write:

SELECT TRIM(BOTH ' ' FROM title) as new_title
FROM post;

Alternatively, you can use the shorter version without the BOTH keyword; by default, TRIM will treat this as a BOTH operation.

SELECT TRIM(' ' FROM title) as new_title
FROM post;

Here’s the result of both queries:

Discussion:

Use the TRIM function if you want to trim a string in a table. This function allows you to remove a specific character from the beginning or end of a string, or from both ends. This function takes the following arguments:

• An optional keyword specifying the ends from which to trim. Possible keywords are:
  • BOTH (remove from the beginning and the end). This is the default option if no keyword is given.
  • TRAILING (remove from the end)
  • LEADING (remove from the beginning)
• The character you want to trim from the string.
• The FROM keyword, followed by the the string to be trimmed.

In our example, that looks like:

TRIM(BOTH ' ' FROM title)

The example below removes the space at the end of each title:

SELECT TRIM(TRAILING ' ' FROM title) as new_title
FROM post;

The query returns the title column without a space at the end. Notice that the spaces at the beginning are left untouched.

You can write a similar query to trim the space at the beginning if you use the LEADING keyword instead:

SELECT TRIM(LEADING ' ' FROM title) as new_title
FROM post;