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');
SQL TRIM() - Setup for examples

With this setup complete, we can run the TRIM() function examples to test and view results in the products table.

Examples: Using TRIM() in SQL Queries

We’ll go through examples demonstrating the TRIM() function in SQL, using sample data from a products table with fields product_code and product_name.

SQL TRIM() - products table data for examples

1. Removing Leading and Trailing Spaces

To remove any leading or trailing spaces from the product_name column:

</>
Copy
SELECT product_name, 
       TRIM(product_name) AS cleaned_product_name
FROM products;

This query removes whitespace from both the beginning and end of each product_name.

SQL TRIM() - Example: Removing Leading and Trailing Spaces

2. Removing Leading Characters

To remove any leading zeros from the product_code field:

</>
Copy
SELECT product_code, 
       TRIM(LEADING '0' FROM product_code) AS trimmed_product_code
FROM products;

This query removes all leading zeros from each product_code, useful for normalizing codes with variable-length formatting.

SQL TRIM() - Example: Removing Leading Characters

3. Removing Trailing Characters

To remove any trailing hyphens from the product_name field:

</>
Copy
SELECT product_name, 
       TRIM(TRAILING '-' FROM product_name) AS trimmed_product_name
FROM products;

This query removes all trailing hyphens from product_name, which is helpful for cleaning up product names in inventory records.

SQL TRIM() - Example: Removing Trailing Characters

4. Removing Specific Leading and Trailing Characters

To remove both leading and trailing underscores from the product_code field:

</>
Copy
SELECT product_code, 
       TRIM(BOTH '_' FROM product_code) AS trimmed_code
FROM products;

This query removes underscores from both ends of each product_code, making data standardized for reporting.

SQL TRIM() - Example: Removing Specific Leading and Trailing Characters

FAQs for SQL TRIM()

1. What does the SQL TRIM() function do?

The TRIM() function removes specified characters from the beginning, end, or both sides of a string.

2. Can TRIM() handle NULL values?

If the input string is NULL, TRIM() returns NULL.

3. Is TRIM() supported in all SQL databases?

Yes, TRIM() is supported by SQL Server, MySQL, PostgreSQL, and other SQL databases.

4. How is TRIM() different from LTRIM() and RTRIM()?

TRIM() can remove characters from both sides of a string, while LTRIM() removes only leading characters and RTRIM() removes only trailing characters.

5. Can TRIM() be used with non-space characters?

Yes, TRIM() can remove any specified character from the beginning, end, or both sides of a string, not just spaces.