SQL Server 2016 or older versions doesn’t have a straight forward string manipulation function to trim both the leading and trailing unwanted spaces. There are workarounds to use LTRIM to remove the leading space and RTRIM to remove the trailing space separately. The good news is that the built-in TRIM function is introduced in the SQL Server 2017 version along with other string functions like translate() and concat_ws(). This new function will remove the spaces at both sides of the string in one operation and makes your code clean.
TRIM() in SQL Server 2017 and Higher
TRIM() function is introduced in SQL Server 2017. In all the previous versions, we have only LTRIM() to remove the leading spaces from the string and RTRIM() to remove the trailing spaces. The newly introduced function can be used to remove the unwanted leading and trailing spaces from a string in one go. It can also be used to remove characters other than space from the beginning and ending of the string.
Using the built-in TRIM Function
The syntax of trim is TRIM([ characters FROM] string) . If you just string without using characters FROM, then it will trim off the spaces on both sides of the string. If you use ‘Characters’ FROM, then it will look of the specific characters at the starting and end of the string and removes them.
Example For trimming space alone
SELECT TRIM(' My Tec Bits ')
/* Result */
'My Tec Bits'
Example For trimming characters
SELECT TRIM( '.! ' FROM '@ My Tec Bits !..') AS Result; /* Result */ '@ My Tec Bits'
TRIM Equivalent in SQL Server 2016 till 2005
In SQL Server 2016 and lower versions,if you try to use the TRIM function, you will get an error stating ‘TRIM’ is not a recognized built-in function name. So in the older versions of SQL Server, the equivalent of the built-in function is using the LTRIM and RTRIM together like LTRIM(RTRIM(‘ String ‘)). If you think this technique makes the code look ugly, then you can enclose this method in a UDF and use it like this:
Create Function dbo.TRIM(@val NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
BEGIN
RETURN LTRIM(RTRIM(@val))
END
GO
/* Using the UDF */
Select dbo.TRIM(' My Tec Bits ') /* For Non-Unicode string */
Select dbo.TRIM(N' ÃÑÉÉ ') /* For Unicode string */
TRIM Equivalent in SQL Server 2000
In version 2000 of SQL Server also you can use LTRIM and RTRIM together to achieve both side trimming functionality. If you are inclined to create a UDF, then there is a minor change from the UDF we have created before. SQL Server 2000 does not have the Varchar(Max) data type. So we have to use the Varchar(8000) data type instead. The UDF will be like this:
Create Function dbo.TRIM(@val VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
RETURN LTRIM(RTRIM(@val))
END
GO
/* Using the udf */
Select dbo.TRIM(' My Tec Bits ')
Reference
- About the built-in trim() Transact-SQL function in Microsoft docs.