SQL TRIM Function

Summary: in this tutorial, you’ll learn how to use the SQL TRIM function to remove specified characters from the start and the end of a string.

Introduction to SQL TRIM function #

In SQL, the TRIM function allows you to remove specified characters from the start and end of a string.

Here’s the syntax of the TRIM function:

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

In this syntax:

characters are the characters you want to remove from the input string. If you do not specify characters, the TRIM() function will remove whitespace by default.
string is the input string you want to remove the specified character
LEADING, TRAILING and BOTH specifies which part of the string you want ot remove the specified characters. The LEADING, TRAILING, and BOTH are the start, the end, or both ends of a string.

The TRIM() function returns NULL if characters or string is NULL.

Basic SQL TRIM function examples #

The following statement uses the TRIM function with the TRAILING option to remove the character * at the end of a string:

SELECT
  TRIM(
    TRAILING '*'
    FROM
      '***SQL**'
  ) result;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 result
--------
 ***SQLCode language: plaintext (plaintext)

The following query uses the TRIM function with the LEADING option to remove all character * from the start of a string:

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

Try it

Output:

 result
--------
 SQL**Code language: plaintext (plaintext)

The following example uses the BOTH option to remove the character * from the start and the end of a string:

SELECT
  TRIM(
    BOTH '*'
    FROM
      '***SQL**'
  ) result;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 result
--------
 SQL

Removing leading zeros #

The following query uses the TRIM function to remove the leading zeros of a string:

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

Output:

 result
--------
 1900Code language: SQL (Structured Query Language) (sql)

Cleaning up data in the database #

The following SELECT statement uses the TRIM function in the UPDATE statement to remove 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)

Try it

Summary #

Use the TRIM function to remove a specified character from a string.
Use the LEADING option to remove a specified character from the start of a string.
Use the TRAILING option to remove a specified character from the end of a string.
Use the BOTH option to remove a specified character from both ends of a string.

Databases #

Was this tutorial helpful ?