The SQL TRIM Function: How it Works and When to Use It

Databases often contain unwanted leading and trailing spaces or other characters around a data string. If these aren’t removed or trimmed, the data will return incorrect matches for queries with WHERE and other clauses. The SQL TRIM function is one of the most frequently used SQL operations for trimming string type data. It removes all spaces and other specified characters from a text string except for the single spaces between words. 

However, in SQL Server and other Relational Database Management Systems (RDBMs), this function may require some refinement to ensure it returns the desired results. Here’s a look at how the TRIM function works in SQL Server, Oracle, and other databases to remove irregular spacing and inappropriate characters from data strings and tables.

If you don’t have SQL Server, you can install SQL Server Developer edition for free. Just follow the link for more details.

Using the SQL TRIM Function in Relational Database Management Systems

SQL is the industry standard programming language for querying relational databases. It is the language behind today’s most widely used RDBMs. These systems collect and manage data from different sources, but that data can arrive with formatting issues, such as irregular spacing and inappropriate characters.

If your database receives text from another application or database, any unusual spacing, or incompatible characters, it can interfere with the database’s ability to read it and return the correct response. In those situations, you can apply TRIM to eliminate the extra spaces or characters.

The TRIM function was originally designed to trim blank spaces, known as the 7-bit ASCII space character (ASCII value 32), from text, but it can now operate on other characters.

The Syntax of the SQL TRIM Function

By default, the SQL TRIM function removes the spaces and specified characters on either side of a text string. If there aren’t any characters identified, TRIM will remove unwanted spaces. You can also use LTRIM or RTRIM to remove characters from only the leading (left) or trailing side (right) of the string, respectively.

In these cases, you should use the following syntax:

TRIM ( string ); RTRIM ( string ); LTRIM ( string );

You can optionally specify individual characters to be trimmed, and TRIM will remove them at the very beginning or end of a string. You do not have this option when using LTRIM or RTRIM. When using TRIM in this case, you should use the following syntax:

TRIM ( characters FROM string );

The SQL TRIM function also removes characters or spacing from entire tables. It can even be used with the UPDATE command to eliminate words, but it treats each letter in the word as a separate character. 

For example, if you want to remove any of the letters in the word ‘MANAGER’ from a table, you can execute the TRIM function with syntax that looks like this:

UPDATE [table] SET JobTitle = TRIM (‘Manager’ FROM JobTitle);

This trims the characters M,A,N,A,G, E, and R from the table at the very beginning and very end of the string. However, as soon as any other character is encountered, that side of the string won’t be trimmed anymore. 

For example, if the string reads:

TRIM (‘Manager’ FROM ‘Research and Development Manager’);

Here, the output is ‘search and Development’. The ‘R’ and ‘e’ are trimmed from the beginning of the string, but because the next letter (‘s’) is not part of ‘Manager’, the trimming stops. All of the letters in ‘Manager’ are removed at the end of the string. The next character is a space, which is not part of the string ‘Manager’, so the trimming stops.

You can also write this command with individual characters separated by commas:

TRIM (‘m,a,n,a,g,e,r’ FROM ‘Research and Development Manager’);

Using TRIM in SQL Server

SQL Server is based on ISO SQL syntax, but the way it executes the TRIM function depends on the version of SQL Server you are using. 

SQL Server 2016 or earlier uses the LTRIM function to remove spaces or characters on the leading (left) side of a string and RTRIM to remove them on the trailing side (right). In SQL Server 2017 or later, you can use the new TRIM function. This acts on specified characters or spaces wherever specified within the string.

This means that if you use the TRIM function in SQL Server 2016 or earlier, the system throws an error that says, “TRIM is not a recognized built-in function name.” For example, running this script in SQL Server 2016 results in an error, but works in SQL Server 2017.

DECLARE @String2 AS VARCHAR(20) SET @String2 = ‘Sentence’ SELECT TRIM(@String2) AS TrimmedValue GO

Using the LTRIM and RTRIM Functions in SQL

Since earlier versions of SQL Server don’t support the TRIM function itself, its workaround separates the TRIM function into two specific operations: RTRIM and LTRIM. LTRIM removes any spaces at the start of the string (the leading side), while RTRIM removes them at the end of the string (the right, or trailing, side). 

These examples show how these two functions work:

SELECT LTRIM(‘ Sentence ‘); /* Result of LTRIM(): ‘Sentence ‘ */ SELECT RTRIM(‘ Sentence ‘); /* Result of RTRIM(): ‘ Sentence’ */

To remove both the leading and trailing spaces from a string in SQL Server 2016 or earlier, you’ll need to combine, or nest, the RTRIM() and LTRIM() functions in a statement. The syntax for this operation looks like this:

DECLARE @String2 AS VARCHAR(20) SET @String2 = ‘ Sentence ‘ SELECT RTRIM(LTRIM(@String2)) AS TrimmedValue

The output of this script appears as the word ‘Sentence’ without any spaces before or after the word.

Conclusion

Trimming functions in SQL help keep data clean and accurate by eliminating unwanted spaces and characters from text strings. This is an important step when importing data from other databases and input sources.

Functions that can be used include TRIM, LTRIM, and RTRIM in SQL-based RDBMs, depending on the operation required. The TRIM function, which cannot be used in SQL Server 2016 or below, applies to both sides of a string, and you can specify the characters or spaces to be removed. You can also use RTRIM and LTRIM to remove from either the right- or left-hand side of a string. The TRIM function can be applied across entire tables to remove designated characters from every row in the table, or to single data strings. Go ahead and try using these functions yourself because the best way to learn SQL is by writing SQL.

To learn how to use this and other functions, have a look at my Udemy course Querying Microsoft SQL Server with Transact-SQL. In this course, we’ll look at the SELECT statement’s syntax and its six principal clauses — SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY — before looking at creating views, procedures, functions, and more.

Thank you very much for reading this, and keep learning!

---

Frequently Asked Questions

What is TRIM in SQL?

The TRIM function in SQL is an operation that removes unwanted spaces or other characters from the beginning and/or ending of a text string.

How do I trim a row in SQL?

To trim a complete database row in SQL, first SELECT the data in the row and use TRIM on every column. Then use UPDATE to insert the trimmed results back into the table. Here is an example of one way to do it:

UPDATE my_table SET col1 = TRIM(col1); UPDATE my_table SET col2 = TRIM(col2);

How do I trim a SELECT query?

To trim the data coming back from a SELECT query, apply TRIM to each column value. Here is an example:

SELECT TRIM(col1), TRIM(col2), TRIM(col3) FROM my_table;