SQL TRIM()

The SQL TRIM() function removes leading, trailing, or both leading and trailing spaces (or other specified characters) from a string.

The TRIM() function is useful for cleaning up data by removing unnecessary whitespace or unwanted characters from text fields.

The TRIM() function is supported by most SQL databases, including SQL Server, MySQL, and PostgreSQL.

In this tutorial, we will go through SQL TRIM() String function, its syntax, and how to use this function in SQL statements for string operations, with the help of well detailed examples.

Syntax of SQL TRIM() Function

The basic syntax of the SQL TRIM() function is:

</>
Copy
TRIM([LEADING | TRAILING | BOTH] characters FROM string);

Each part of this syntax has a specific purpose:

  • LEADING: Removes specified characters from the beginning of the string.
  • TRAILING: Removes specified characters from the end of the string.
  • BOTH: Removes specified characters from both the beginning and end of the string (default).
  • characters: The characters to remove. If not specified, whitespace is removed.
  • string: The original text from which characters will be removed.

The TRIM() function returns the modified string with specified characters removed from the beginning, end, or both.

Setup for Examples: Creating the Database and Table

We’ll create a sample products table with fields product_code and product_name. Follow these steps to set up the data for the TRIM() function examples.

1. First, create a new database called inventory:

</>
Copy
CREATE DATABASE inventory;

2. Select the inventory database to work with:

</>
Copy
USE inventory;

3. Create a table named products with the fields product_id, product_code, and product_name:

</>
Copy
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_code VARCHAR(20),
    product_name VARCHAR(100)
);

4. Insert sample data into the products table to use with the TRIM() function examples:

</>
Copy
INSERT INTO products (product_code, product_name)
VALUES
('00123', '   Laptop   '),
('00234', 'Phone-'),
('_03456_', '_Tablet_'),
('7890', 'Camera');