SQL Trim – How to Trim Data?

sql trimIn a database you often find leading and trailing spaces around a string. You must have wondered how to remove these spaces. TRIM is one of the most frequently used operations for trimming string type data. The TRIM function is used to remove specified prefix or suffix from a string. It removes all spaces from a text string except the single spaces between words.

What’s SQL?

SQL or Structured Query Language is the standard language used to communicate with Relational Database Management Systems (RDMS) such as Oracle, Microsoft SQL Server, Sybase, Informix, and so on. It is the backbone of all relational databases and offers a flexible interface for databases of all shapes and sizes. It is used as the basis for all user and administrator interactions with the database. With SQL, you can build databases, enter data into the database, manipulate data, and query the database data. The output or results from the query are often used in analysis or to make business decisions. SQL is a simple, friendly, English-like language that is relatively easy to learn and is being extensively used by database users, administrators, and developers all over the world.

If you are yet to get familiarized with SQL and looking to learn SQL database and how to write SQL queries, then Introduction to SQL Training and SQL Database for Beginners are great courses to start your learning process.

As the demand for database developers are rising in different sections of the industry, you will call the shots if you learn SQL. Most database vendors are aligning their databases with the ANSI/ISO SQL standard. So when you learn how to handle SQL databases, you’ll be prepared to move from one database to another. The SQL language hasn’t changed much over the decades and once you understand the basics of SQL, you can keep upgrading and building your knowledge so that you can add more features and functionality to your database. By knowing SQL, as a DBA, you’ll be able to make better decisions and move poorly performing code out of the database. Through experience and practice you will soon be able to spot the problems even before they strike.

TRIM Function in Different Databases

If you receive text from another application with irregular spacing, then you can apply TRIM on it to take care of the extra unwanted spacing. The TRIM function was originally designed to trim the 7-bit ASCII space character (value 32) from text.

These are how the TRIM function appears in different syntax in different databases:

  • MySQL: TRIM( ), RTRIM( ), LTRIM( )
  • Oracle: RTRIM( ), LTRIM( )
  • SQL Server: RTRIM( ), LTRIM( )

TRIM in SQL

You will be surprised to know that SQL Server has no TRIM function. If you use a TRIM function in SQL, then it will throw an error saying ‘TRIM’ is not a recognized built-in function name.’

So the following script when executed in SQL will throw an error.

Syntax:

DECLARE @String2 NVARCHAR(MAX)

SET @String2 = ‘Sentence’

SELECT TRIM(@String2) TrimmedValue

GO

RTRIM and LTRIM Functions in SQL

If SQL does not support the TRIM function then how do you delete the extra spaces from a string?

SQL has a solution, it has two functions, RTRIM and LTRIM.

  • RTRIM – Removes the spaces on the right side, or the leading spaces from a string
  • LTRIM – Removes the spaces on the left side, or the trailing spaces from a string

The SQL RTRIM function name is not case sensitive and using all lowercase letters will provide the same results.

Let’s see through examples 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 get the same effect of a TRIM function in SQL and to remove both the leading and trailing spaces from a string, we need to combine or nest the RTRIM () and LTRIM () functions. If we combine them in a statement, then it will work and will not throw an error.

Let’s see what the syntax for this operation is.

Syntax:

DECLARE @String2 NVARCHAR(MAX)

SET @String2 = ‘ Sentence ‘

SELECT @String2 OriginalString, RTRIM(LTRIM(@String2)) TrimmedValue

GO

The output of the above script will show up as the word ‘Sentence’ with no spaces before or after the word.

Trimming Function for Repeated Use

You can create a User Defined Function or UDF and call this UDF whenever you have to trim a word or column.

Syntax:

CREATE FUNCTION dbo.TRIM1(@string VARCHAR(MAX))

RETURNS VARCHAR(MAX)

BEGIN

RETURN LTRIM(RTRIM(@string))

END

GO

Let’s see what output we get when we apply and run the above UDF on a string which has leading and trailing spaces around it.

Syntax:

SELECT dbo.TRIM1(‘  ahead behind  ‘)

Output:

The returned string will appear as follows without any spaces:

‘ahead behind’

Conclusion

We discussed the different options we can use to replicate the TRIM function in SQL. Well, till SQL includes a single function for the trimming operation, we will keep using the syntax mentioned above. So next time if someone comes up and tells you that he is not being able to trim or remove extra spaces in SQL, then you will surely have a ready answer and solution for him.