SQL TRIM: How to Remove Unwanted Characters from a String

Summary: in this tutorial, we will introduce you to the SQL TRIM function that removes both leading and trailing characters from a string.

Introduction to SQL TRIM function

The TRIM function allows you to trim leading and/or trailing characters from a string. The following shows the syntax of the TRIM function.

TRIM([LEADING | TRAILING | BOTH] trim_character FROM source_string);Code language: SQL (Structured Query Language) (sql)

First, specify the trim_character, which is the character that the TRIM function will remove. If you do not specify trim_character the TRIM function will remove the blank spaces from the source string.

Second, place the source_string followed the FROM clause.

Third, the LEADING, TRAILING, and BOTH specify the side of the source_string that the TRIM function will remove the trim_character.

If you specify LEADING, the TRIM function will remove any leading characters that match the trim_character.
If you specify TRAILING, the TRIM function will remove any trailing characters that match the trim_character.
If you specify BOTH or none of three, the TRIM function will remove both leading and trailing characters that match the trim_characters.

The TRIM function returns NULL if either trim_character or source string is NULL.

SQL TRIM examples

Suppose we have a string that contains two spaces at the beginning and one space at the end of the string ( ' SQL '). The length of the string is six.

SELECT LENGTH('  SQL ');Code language: SQL (Structured Query Language) (sql)

 length
--------
      6
(1 row)Code language: SQL (Structured Query Language) (sql)

The following statement uses the TRIM function with the LEADING option to remove all leading spaces of the string.

SELECT TRIM(LEADING FROM '  SQL ');
 trim
-------
 SQL
(1 row)Code language: SQL (Structured Query Language) (sql)

You can test it by using the LENGTH function. The length of the result string must be four because the TRIM function removes two spaces at the beginning of the string.

SELECT LENGTH(TRIM(LEADING FROM '  SQL '));
 LENGTH
--------
      4
(1 row)Code language: SQL (Structured Query Language) (sql)

The following statement uses the TRIM function with the TRAILING option removes all trailing blank spaces of the string.

SELECT TRIM(TRAILING FROM '  SQL ');
 trim
-------
   SQL
(1 row)Code language: SQL (Structured Query Language) (sql)

The length of the string must be five because the TRIM function removes one space at the end of the string.

SELECT LENGTH(TRIM(TRAILING FROM '  SQL '));
 length
--------
      5
(1 row)Code language: SQL (Structured Query Language) (sql)

The following statement removes both leading and trailing spaces of the string.

SELECT TRIM('  SQL ');
 trim
-------
 SQL
(1 row)Code language: SQL (Structured Query Language) (sql)

Of course, the length of the result string must be three.

SELECT LENGTH(TRIM('  SQL '));
 length
--------
      3
(1 row)Code language: SQL (Structured Query Language) (sql)

The following statement removes leading zero of a string because the trim_character is ‘0’.

SELECT TRIM(LEADING '0' FROM '00001900');Code language: SQL (Structured Query Language) (sql)

 trim
-------
 1900
(1 row)Code language: SQL (Structured Query Language) (sql)

The following statement removes both leading and trailing @ of the string.

SELECT TRIM('@' FROM '@2000@');Code language: SQL (Structured Query Language) (sql)

 trim
-------
 2000
(1 row)Code language: SQL (Structured Query Language) (sql)

Sometimes, when users submit the form that contains input fields such as first name, last name, email, phone, etc., the data may contain leading and/or trailing spaces. Before insert or update the database, you need to validate and remove the spaces. If you are not, the data not clean that results in incorrect matching using the WHERE clause.

If you need to clean up data, you can use the UPDATE statement with the TRIM function to remove all unwanted characters from the database especially the spaces.

For example, the following statement removes all leading and trailing spaces in the first_name, last_name, email, and phone_number columns of the employees table.

UPDATE employees 
SET 
    first_name = TRIM(first_name),
    last_name = TRIM(last_name),
    email = TRIM(email),
    phone_number = TRIM(phone_number);Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the SQL TRIM function to remove unwanted characters from a string.

Was this tutorial helpful ?