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 spaces or characters 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, and it removes all spaces and other specified characters from a text string except for the single spaces between words.
But in SQLServer 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.
What is SQL — and Why Should You Learn It?
Structured Query Language (SQL) is the standard programming language used to communicate with Relational Database Management Systems (RDMS) such as Oracle, Microsoft SQL Server, Sybase, and Informix. SQL provides a flexible interface for databases of all kinds and operates as the basis for all user and administrator interactions with the database. With SQL and the languages based on it, users can organize and manage data from many different input sources.
SQL is a simple, user-friendly language with English-like syntax that’s relatively easy to learn and widely used by database users, administrators, and developers around the world. With SQL, you can build databases, enter data and manipulate it, and query the database data in multiple ways for use in analyses or business decision making.
The demand for database developers is rising across all sectors of the industry, and most database vendors are aligning their databases with the ANSI/ISO SQL standard. When you know how to handle SQL databases, you’ll be prepared to move from one database to another and keep upgrading your abilities to add more features and functionality to your databases. Thanks to your familiarity with SQL, you’ll be able to move poorly performing code out of the database, make better decisions, and spot problems before they strike.
Using the SQL TRIM Function in Relational Database Management
SQL is the industry standard programming language for querying relational databases. It is the language behind today’s most widely used RDBMs, such as SQL Server. These systems are used to collect and manage data from many 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, it can have irregular spacing or inappropriate characters that can interfere with the ability of the database to read it and return the correct response. In those situations, you can apply TRIM to eliminate the extra unwanted spaces or characters, even if the data comes from a different database.
The TRIM function was originally designed to trim the 7-bit ASCII space character (value 32) from text, but it can now operate on other characters in the syntax of different databases. In those databases, the TRIM function has been modified in order to be recognized. Here’s how the TRIM function appears in some commonly used databases:
- MySQL: TRIM( ), RTRIM( ), LTRIM( )
- Oracle: RTRIM( ), LTRIM( )
- SQL Server: RTRIM( ), LTRIM( )
The Syntax of the SQL TRIM Function
By default, the SQL TRIM function removes specified characters and spaces on either side of a text string, and you can specify which string SQL TRIM should operate on with the element STRING.
If no characters are identified, TRIM will remove unwanted spaces by default. You can specify individual characters to be trimmed with the TRIMCHARACTER command, and TRIM will remove them wherever they occur. You can also use LTRIM or RTRIM to remove characters only from the leading (left) or trailing side (right) of the string, respectively.
SQL TRIM can also be used to remove 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 the word ‘MANAGER” from a table, you could execute the TRIM function with syntax that looks like this:
UPDATE [table] SET JobTitle = TRIM (‘Manager’ FROM)
This would trim the characters M,A,N,A,G, E, and R from the table. But if any other parts of the string also contain those characters, they will be removed there as well.
For example, if the string reads:
TRIM (‘Manager’ FROM ‘Research and Development Manager’):
Here, the output is ‘search and Development‘ because the designated letters were trimmed.
This command could also be written with individual characters separated by commas:
TRIM (‘m,a,n,a,g,e,r’ FROM).
The TRIM function will operate in the same way:
TRIMCHARACTER ‘Manager’ UPDATE
Using TRIM in SQL Server
Some databases use SQL syntax, but they lack the ability to recognize the TRIM function itself. For example, SQL Server is based on 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 LTRIM 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, though, both those functions have been assimilated into the SQL TRIM function, which acts on specified characters or spaces wherever specified within the string.
This means that if you use the TRIM function in SQLServer, the system throws an error that says, “TRIM is not a recognized built-in function name.” For example, when executed in SQL Server, this script may throw an error:
DECLARE @String2 NVARCHAR(MAX) SET @String2 = ‘Sentence’ SELECT TRIM(@String2) 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. Wherever needed, LTRIM removes the spaces or characters at the start of the string (the leading side), while RTRIM removes them at the end of the string (the right, or trailing, side).
Without either of these specifications, the TRIM function operates to remove spaces or characters wherever they occur in the string, either on the leading side or the trailing side at the end of the string. If unwanted characters occur on both sides of the string, the TRIM function used alone removes them all.
The SQL RTRIM and LTRIM function names are not case sensitive, so using all lowercase letters will provide the same results.
These examples show how these two functions work:
Syntax for LTRIM ():
SELECT LTRIM(‘ Sentence ‘); Result of LTRIM (): ‘Sentence ‘
Syntax for RTRIM ():
SELECT RTRIM(‘ Sentence ‘); Result of RTRIM (): ‘ Sentence’
How to Achieve TRIM in SQL
To remove both the leading and trailing spaces from a string, we’ll need to combine, or next, both the RTRIM () and LTRIM () functions in a statement. That allows the TRIM function to work without throwing an error. The syntax for this operation looks like this:
DECLARE @String2 NVARCHAR(MAX) SET @String2 = ‘ Sentence ‘ SELECT @String2 OriginalString, RTRIM(LTRIM(@String2)) TrimmedValue GO
The output of this script will appear as the word ‘Sentence’ with no spaces before or after the word.
Creating a Trimming Function for Repeated Use
You can create a UDF (User Defined Function) for trimming and call this function whenever you need to trim a word or column in your database. UDFs can work in all currently supported versions of SQL server to remove spaces and characters using specific parameters set for the action, but they can’t be used for any actions that modify the database state itself.
The syntax for creating a User Defined Function looks like this:
CREATE FUNCTION dbo.TRIM1(@string VARCHAR(MAX)) RETURNS VARCHAR(MAX) BEGIN RETURN LTRIM(RTRIM(@string)) END GO
Here’s the output when we apply and run this UDF on a string that has both leading and trailing spaces around it:
SELECT dbo.TRIM1(‘ ahead behind ‘) Output: ‘ahead behind’
The returned string appears without any spaces before or after the text.
The TRIM function in SQL helps to keep data clean and accurate by eliminating unwanted spaces and characters from text strings, which is an important consideration when importing data from other databases and input sources.
The SQL TRIM function may be expressed as TRIM, LTRIM, and RTRIM in SQL-based RDBMs, depending on the operation required. Because SQL TRIM applies to spaces and characters on both sides of a string, the RTRIM and LTRIM syntax can specify the characters or spaces to remove in order to achieve the desired results in SQL Server and other databases such as MYSQL and Oracle. The TRIM function can be applied across entire tables as well as in single data strings to remove designated characters from every row in the table.
Top courses in SQL
SQL students also learn
Empower your team. Lead the industry.
Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.