How to Trim Strings in SQL

Problem:

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

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:

• A keyword specifying the ends from which to trim (optional—BOTH by default).
• The character you want to trim from the string.
• The FROM keyword, followed by the name of the string column to be trimmed.

In our example, that looks like:

TRIM( BOTH ' ' FROM title )

As mentioned above, TRIM can take other keywords as its first argument. In addition to trimming from both ends, it allows us to remove a character only from the end of a string or only from the beginning:

• TRAILING (remove from the end)
• LEADING (remove from the beginning)

The example below removes the space at the end of each title (but could just as well be used to trim the space at the beginning if you use the LEADING keyword instead):

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.