Trim is one of the most frequently used operation over String data types. A developer often come across a scenario where they have the string with leading and trailing spaces around string. If your business logic suggests that the logs around the spaces are not useful they should be trimmed. However, in SQL Server there is no TRIM function. When a TRIM function is used, it will throw an error.
For example, here is the script when executed it will throw an error.
-- The following will throw an error DECLARE @String1 NVARCHAR(MAX) SET @String1 = ' String ' SELECT TRIM(@String1) TrimmedValue GO
The above script will return following error:
Msg 195, Level 15, State 10, Line 4
‘TRIM’ is not a recognized built-in function name.
Let us not everything, why this simple function is not implemented, but try to resolve how we can achieve the result of the same function. SQL Server has two functions which, when nested can give us the same result as a TRIM function.
1) RTRIM – Removes the Spaces on the right side (or leading spaces) of the string
2) LTRIM – Removes the Spaces on the left side (or trailing spaces) of the string
We can combine them as following and it will not throw an error.
-- The following will work DECLARE @String1 NVARCHAR(MAX) SET @String1 = ' String ' SELECT @String1 OriginalString, RTRIM(LTRIM(@String1)) TrimmedValue GO
Let us see the same concept in following SQL in Sixty Seconds Video:
Additionally, if you want, you can create a user defined function which is used RTRIM and LTRIM and can use the function when trim functionality is required.
-- Create Function
CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
RETURN LTRIM(RTRIM(@string))
END
GO
SELECT dbo.TRIM(' String ')
GO
Now when we are on the topic of the TRIM function, let me remind you one very important impact of this function if used in the WHERE clause. If any function is used in the WHERE clause, it will negatively impact on the performance of the query. SQL Server has to process the function on whole column leading Table Scan or Index Scan instead of Index Seek. This will increase the resource utilization and lead to poor performance. However, using this function in SELECT statement does not degrade performance much. In simple words – please be mindful of using any functions. Use the functions when you absolutely need it or enforcing business needs.
Related Tips in SQL in Sixty Seconds:
- SQL SERVER – TRIM() Function – UDF TRIM()
- TRIM() Function – User Defined Function
- Enhenced 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?
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
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
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