SQL Server String TRIM() Function

SQL Server TRIM() Function

The SQL Server TRIM() function is used to remove leading and trailing spaces from a string. This function is useful for cleaning up string data by eliminating unwanted spaces at both ends of the string.

Syntax

SELECT TRIM(string);

The TRIM() function takes a single argument:

  • string: The string from which to remove leading and trailing spaces.

Example SQL Server TRIM() Function Queries

Let's look at some examples of SQL Server TRIM() function queries:

1. Basic TRIM() Example

SELECT TRIM('   Hello World   ') AS result;

This query removes the leading and trailing spaces from the string ' Hello World '. The result will be:

result
-------------
Hello World

2. TRIM() with a Column

SELECT first_name, TRIM(first_name) AS trimmed_first_name
FROM employees;

This query removes the leading and trailing spaces from the first_name column for each employee. The result will show the first_name and the trimmed version as trimmed_first_name.

3. TRIM() with a Variable

DECLARE @myString VARCHAR(50);
SET @myString = '   SQL Server   ';
SELECT TRIM(@myString) AS result;

This query uses a variable to store a string and then removes the leading and trailing spaces. The result will be:

result
-----------
SQL Server

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the TRIM() function.

Step 1: Creating a Table

This step involves creating a new table named example_table to store some sample data.

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    description VARCHAR(50)
);

In this example, we create a table named example_table with columns for id and description.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the example_table.

INSERT INTO example_table (id, description) VALUES (1, '   Apple   ');
INSERT INTO example_table (id, description) VALUES (2, '   Banana   ');
INSERT INTO example_table (id, description) VALUES (3, '   Cherry   ');

Here, we insert data into the example_table.

Step 3: Using the TRIM() Function

This step involves using the TRIM() function to remove leading and trailing spaces from the description column.

SELECT id, description, TRIM(description) AS trimmed_description
FROM example_table;

This query retrieves the id, description, and the trimmed version of the description column for each row in the example_table. The result will be:

id  description   trimmed_description
--- ------------- -------------------
1   Apple         Apple
2   Banana        Banana
3   Cherry        Cherry

Conclusion

The SQL Server TRIM() function is a powerful tool for removing leading and trailing spaces from a string. Understanding how to use the TRIM() function and its syntax is essential for effective string manipulation and data processing in SQL Server.