SQL Server TRIM() Function

In SQL Server, the TRIM() function is used to remove leading and trailing spaces from a string. This function is very useful for manipulating and cleaning up strings, especially when performing string matching or comparisons.

Syntax

The syntax of the TRIM() function is as follows:

TRIM( [ BOTH | LEADING | TRAILING ] [ trim_character ] FROM string )

where the parameters are defined as follows:

  • BOTH: removes trim_character from both the beginning and end of the string.
  • LEADING: removes trim_character from the beginning of the string.
  • TRAILING: removes trim_character from the end of the string.
  • trim_character: the character or set of characters to be removed, default is a space.
  • string: the string to be processed.

Use Cases

The TRIM() function is often used for string matching and comparisons. For example, when we need to compare two strings, we can use the TRIM() function to avoid comparison errors caused by spaces at the beginning or end of the string.

In addition, the TRIM() function can also be used in some data cleaning scenarios to remove unnecessary spaces from strings.

Examples

Here are two examples of using the TRIM() function.

Example 1

Suppose we have a string ' Hello World! ' and we want to remove the leading and trailing spaces. We can easily do this using the TRIM() function:

SELECT TRIM('   Hello World!   ') AS trimmed_string;

The result will be:

trimmed_string
Hello World!

Example 2

Suppose we have a table students that contains the names and phone numbers of students, but the names contain spaces. Now we want to query the phone numbers of all students with the last name “Smith”. Since there may be spaces in the name, we need to use the TRIM() function to process the name:

SELECT phone_number
FROM students
WHERE TRIM(LAST_NAME) = 'Smith';

The result will be the phone numbers of all students with the last name “Smith”.

Conclusion

The TRIM() function is a very convenient string processing function in SQL Server. It can be used to clean up strings, especially when performing string matching or comparisons, to help avoid comparison errors caused by spaces at the beginning or end of the string.