The TRIM function removes unwanted spaces from the ends of a string. This 2013 video demonstrates the older LTRIM and RTRIM approach. SQL Server 2017 introduced built-in TRIM, so the original statement that SQL Server has no TRIM function applies only to older versions.
The Method Used in the Original Lesson
LTRIM removes leading spaces on the left. RTRIM removes trailing spaces on the right. Nesting them removes ordinary spaces from both ends. The brackets in this example make the result easier to see:
DECLARE @String1 nvarchar(100) = N' String ';
SELECT N'[' + @String1 + N']' AS OriginalString,
N'[' + RTRIM(LTRIM(@String1)) + N']' AS TrimmedValue;The result is [ String ] before trimming and [String] afterward. On SQL Server versions before 2017, calling the built-in TRIM produces the original error: 'TRIM' is not a recognized built-in function name.
Use Built-In TRIM on SQL Server 2017 and Later
-- SQL Server 2017 or later
DECLARE @String1 nvarchar(100) = N' String ';
SELECT TRIM(@String1) AS TrimmedValue;By default, these functions remove the ordinary space character, not every kind of whitespace. Tabs, line breaks and nonbreaking spaces need an explicit cleaning rule. SQL Server 2022 added the optional LEADING, TRAILING and BOTH keywords for TRIM; that syntax requires database compatibility level 160 or higher.
Watch the Original Video
The Historical User-Defined Wrapper
The original article also showed a small wrapper for older databases. It is retained here for that context. You do not need to create it just to use built-in TRIM on a modern server. This older wrapper takes varchar; use an appropriate Unicode definition if your application needs nvarchar input.
-- Historical wrapper for a database without built-in TRIM.
-- Create only if this function name is not already in use.
CREATE FUNCTION dbo.TRIM(@string varchar(max))
RETURNS varchar(max)
AS
BEGIN
RETURN LTRIM(RTRIM(@string));
END;
GO
SELECT dbo.TRIM(' String ') AS TrimmedValue;
GOBe careful when trimming a table column inside a WHERE condition. Applying a function to the searched column can prevent an efficient index seek, but it does not guarantee a scan in every query. Check the execution plan and clean data where the business rules allow it. Trimming values in the SELECT list still costs work, even when it does not change how rows are located.
Related Tips
- SQL SERVER – TRIM() Function – UDF TRIM()
- TRIM() Function – User Defined Function
- Enhanced TRIM() Function – Remove Trailing Spaces, Leading Spaces, White Space, Tabs, Carriage Returns, Line Feeds
What would you like to see in the next SQL in Sixty Seconds video?
Trimming is not a rule to apply to every string, it is a data-cleaning choice that should match the business requirement.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
18 Comments. Leave new
Hi Pinal,
Small Mistake in below Post
2) RTRIM – Removes the Spaces on the left side (or trailing spaces) of the string.
*Thanks & Regards,*
Srinivas.K
How do you figure? RTrim removes the trailing spaces (i.e. thos which come AFTER the text), which are those on the Right. That’s why the “R” in RTrim. The spaces on the left are leadings spaces because they come first, thus the “L” in LTrim.
Hiii Pinal
In both the point you have written RTRIM function replace point 2 with LTRIM
Thanks Fixed!
Hi pinal
What are the best practices for keeping referential integrity across multiple databases?
question about Trim in comparison statements in where clause when data type is a fixed length char. At one time in SQL Server T-SQL at least it was not possible to get a correct match when look for “WHERE Field1 = ‘at’ ” when Field1 was a char column of length 5. It was necessary to do either “WHERE Field1 = ‘at ‘ ” or “WHERE RTrim(Field1) = ‘at’ “. Based on your recommendations against it are you saying that this is no longer necessary?
Hiii Pinal,
row1 has LEVEL. row2 has _Level(space created at pre),Please help me how to remove this pre space?
I strongly believe one has use the “Replace” function too. This too will work.
SELECT REPLACE(RTRIM(LTRIM(Column_Name)),’ ‘,”) FROM Table
This query works fine in SQL Server 2008 and R2
You have to be careful of the data you are comparing. Including the REPLACE function will remove spaces internally in the column data. If you are looking for a result with a space in it, this won’t work. Example, utilizing REPLACE would take the value “JOHN SMITH” and return “JOHNSMITH”. I would recommend only using REPLACE where you are sure you will need it rather than with every LTRIM or RTRIM call.
Thanks for the tip! I thought for sure that TRIM was a default function of MS SQL but leave it to M$ for being weird. Thanks for clearing this up man!
How about this? Trim spaces(left & right) but will return a null value if the the field only contains spaces.
can you call a user defined function in stored proc as well?
i have a column value ‘Sign_OR2408000391_221215FTO_257049_221215_261215_p2w.xml’ but i need the result to be selected as only ‘FTO_257049’ AFTER FTO_ UPTO THE NEXT _.
Hi Friends..Can you pls let me know that how to remove the space with datas(for example: ‘ HUWH-‘)from a table and this output report is compared to another table field?
Pinal, time to update that article with latest function from SQL 2017 :)
That would be a new article Mr. Trim :)
Thank you so much. It is very helpful.
I enjoyed reading this post.
Thanks for educating the community.
Guru DevoBhava