I just received following question in email by James Louren.
“How come SQL Server 2000, 2005 does not have function TRIM()? Is there any way to get similar results.
What about SQL Server 2008?”
James has asked very interesting question. I have previously wrote about SQL SERVER – TRIM() Function – UDF TRIM(). Today my answer is no different than what I answered in earlier post.
SQL Server does not have function which can trim leading or trailing spaces of any string at the same time. SQL does have LTRIM() and RTRIM() which can trim leading and trailing spaces respectively. SQL Server 2008 also does not have TRIM() function. User can easily use LTRIM() and RTRIM() together and simulate TRIM() functionality.
SELECT RTRIM(LTRIM(' Word ')) AS Answer;
Should give result set without any leading or trailing spaces.
Answer
——
Word
I have created following UDF which everyday when I have to TRIM() any word or column.
CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
RETURN LTRIM(RTRIM(@string))
END
GO
Now let us test above UDF running following statement where there are leading and trailing spaces around word.
SELECT dbo.TRIM(' leading trailing ')
It will return string in result window as
'leading trailing'
There will be no spaces around them. If extra spaces are useless data, when data is inserted in database they should be trimmed. If there is need of spaces in data but in certain cases they should be trimmed when retrieving we can use Computed Columns. Read more about computed columns SQL SERVER – Puzzle – Solution – Computed Columns Datatype Explanation.
Following example demonstrates how computed columns can be used to retrieve trimmed data.
USE AdventureWorks
GO
/* Create Table */
CREATEÂ TABLE MyTable
(
IDÂ TINYINT NOTÂ NULL IDENTITY (1, 1),
FirstCol VARCHAR(150)Â NOTÂ NULL,
TrimmedCol AS LTRIM(RTRIM(FirstCol))
) ON [PRIMARY]
GO
/* Populated Table */
INSERTÂ INTO MyTable
([FirstCol])
SELECT 'Â Leading'
UNION
SELECT 'Trailing '
UNION
SELECT ' Leading and Trailing '
UNION
SELECT 'NoSpaceAround'
GO
/* SELECT Table Data */
SELECT *
FROM MyTable
GO
/* Dropping Table */
DROPÂ TABLE MyTable
GO
Above query demonstrates that when retrieving data it retrieves trimmed data in column TrimmedCol. You can see the result set in following image.
Computed columns are created run time and performance may not be optimal if lots of data is being retrieved. We will see some other time how we can improve the performance of Computed Column using Index.
Here is the quick video on the same subject:
[youtube=http://www.youtube.com/watch?v=1-hhApy6MHM]Reference : Pinal Dave (https://blog.sqlauthority.com)
21 Comments. Leave new
It will be the Great Feature whiich is planned to be included in SQl Server 2010 or +
Micr0$0ft
:)
If Microsoft will one day understand how to implement BASIC BASIC functions like TRIM, that alone will be worth a new SQL Server release :P
Speaking of which, are there cases where people actually only need LTRIM or RTRIM, and not TRIM?
Actually I tried this query
SELECT RTRIM(‘ Hai ‘)
the result was like what i have given.
After that i gave alias name and then correct result come.
I want to know that this query must have alias name?
trim() function should remove any space and not only those on left or on right of the string. this is not trim().
No this is fundamentally incorrect. Even the verb “to trim” means to cut off the bit around the edge (like a haircut trim – doesn’t mean to shave off all the hair!). Trim in every language (and other RDBMS I’ve used – and I’ve used a lot) means to cut of white space at eaither end, NOT to remoave all white space. There is already a function to do the latter: Remove (or Replace language/RDBMS dependant). LTRIM(RTIM(‘ string ‘)) is the right way to go, and has little overhead above ‘Trim’ as both require a by-char scan until first non-blank is reached (Rtirm obviously runs backwards from the end).
Hi Pinal,
Did SQL 2008 eliminates the empty space in the right side by default? Coz while executing the below queries, i have got the same result
Select Len(‘ trail test’)
Select Len(‘ trail test ‘)
hi my friend
the answer is so simple
TRIM FUNCTION
..
..
..
return replace(@S,’ ‘,”)
Hi blog.sqlauthority.com,
what’s the return type when function returns nothing.
Hi there, I’m not sure about this trim functionality on sql 2005/2008 is the same structure ? if I create this function on sql 2005 works on 2008 ? I really appreciate if someone could answer to me.
is it applicable for nvarchar ????
I tried for a column but its not working
query used: select LTRIM(RTRIM(Col1)) from [TABLE] order by Col1
Yes it will work for nvarchar too. What di you mean by it is not working?
This function returns string after removing filter.
for exampe – i passed select [Func_FilterDelimted](‘aa,bb,cc,dd,ee’)
outpur – aabbccddee
====
ALTER FUNCTION [dbo].[Func_FilterDelimted]
(
— Add the parameters for the function here
@Deli_Str nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN
— Declare the return variable here
Declare @Lng smallint=0;
Declare @St nvarchar(max)=”;
Declare @comm nvarchar(1)=”;
Declare @i smallint=0;
Declare @Asc smallint=0;
Set @Lng=Len(@Deli_Str);
Begin
set @i=0;
While @i<=@Lng
Begin
Set @comm=substring(@Deli_Str,@i,1);
Set @Asc=Ascii(@comm);
If(@Asc!=44)
Begin
Set @St=@St+@comm;
End;
–Else
–Begin
— Insert into #FilterTable(ServiceID)Values(Cast(@St as int));
–End;
Set @i=@i+1;
End;
End;
— Return the result of the function
RETURN @St
END
Hi,
just now am learning about the SQL server, I cant understand exactly. I ned more explanation. i did with this example :
SELECT RTRIM(LTRIM(‘ Word ‘)) AS Answer
Output : word
But without giving the keywords also same output is generating, wats the difference.
SELECT ((‘ Word ‘)) AS Answer
Output : word – Same output
Not the same output. You can see it from the following example
SELECT ‘a’+RTRIM(LTRIM(‘ Word ‘))+’a’ AS Answer
SELECT ‘a’+((‘ Word ‘))+’a’ AS Answer
ALTER FUNCTION dbo.TRIM(@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
While CharIndex(‘ ‘,@string) > 0
Begin
SELECT @string = LTRIM(RTRIM(REPLACE(@string, ‘ ‘, ‘ ‘)))
exec dbo.trim @string
End
RETURN @string
END
GO
I have imported some data from Excel and in some columns there’s a space before every column value.It is not even removed with LTRIM and RTRIM…Can anybody help?
I have imported some data from excel and it has some columns with spaces before some columns. It has not removed the space with LTRIM(RTRIM) . Can anyone suggest why it is not working?
Arthurkobau,
Excel has TRIM function that remove spaces before and after the string as well as white spaces in the middle of the string. You can TRIM the data in Excel before import to SQL.
Have you checked that it’s really a space?
Maybe it’s some other character (e.g. tab).
You could try SELECT ASCII(your_column). The result would be 32, if it’s a space.
Combination of LTRIM and RTRIM only removes extra spaces before and after the string. The extra spaces in the middle is not eliminated.
TRIM function (as it works in Excel) removes the white spaces in the middle of the string as well. To get a full function of TRIM you should have another function that able to eliminate all extra spaces in the middle of the string become single space.
Thanks a lot! (I was searching for a TRIM in SQL, and on docs.micr… i have found it, tried it and it didn’t work in SQL 2008 R2 (instantly, I din’t tried LTRIM/RTRIM; there, there is no comment about supported SQL versions); then I found citation from Your site in top of google search!)