Trim in SQL – Does it Exist?

Trim in SQLWanted to TRIM, But Didn’t Know How to Do it?

As a developer you must have often come across a scenario where you need to remove the leading and trailing spaces around a string. TRIM is one of the most frequently used operations over 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.

Are you new to SQL and yet to learn how to write SQL queries? If you want to get trained in how to build applications and generate reports, then get going with Introduction to SQL Training and SQL Database for Beginners. These 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

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 is the syntax for this operation.

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.

As you keep climbing the ladder of your career, it is a good idea to keep yourself updated on the latest developments and be ahead in the race. Many firms adopt SQL for their convenience and security of their records. Hence, they look to appoint professionals who have knowledge in SQL and have undergone training in SQL. An SQL certification helps to validate your knowledge and gives you better job opportunities. So if you want to add more feathers to your cap and want certification courses to appear on your resume, then Microsoft SQL Server 2012 Certification Training Exam 70-461 will prepare you for the MCSA or MCSE certification exams.