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.
I have created following UDF which everyday when I have to TRIM() any word or column.
CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))
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
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.
/* 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
SELECT ' Leading'
SELECT 'Trailing '
SELECT ' Leading and Trailing '
/* SELECT Table Data */
/* Dropping Table */
DROP TABLE MyTable
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:
Reference : Pinal Dave (https://blog.sqlauthority.com)
It will be the Great Feature whiich is planned to be included in SQl Server 2010 or +
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).
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
return replace(@S,’ ‘,”)
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
— 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;
— Insert into #FilterTable(ServiceID)Values(Cast(@St as int));
— Return the result of the function
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))
While CharIndex(‘ ‘,@string) > 0
SELECT @string = LTRIM(RTRIM(REPLACE(@string, ‘ ‘, ‘ ‘)))
exec dbo.trim @string
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?
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!)