SQL SERVER – 2008 – TRIM() Function – User Defined Function

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.

SQL SERVER - 2008 - TRIM() Function - User Defined Function ftrim

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)

,
Previous Post
SQLAuthority News – SQL Server 2008 – Microsoft Certifications for 70-432 70-433 70-450 70-452
Next Post
SQL SERVER – 2008 – Enhenced TRIM() Function – Remove Trailing Spaces, Leading Spaces, White Space, Tabs, Carriage Returns, Line Feeds

Related Posts

21 Comments. Leave new

  • It will be the Great Feature whiich is planned to be included in SQl Server 2010 or +
    Micr0$0ft

    :)

    Reply
  • 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?

    Reply
  • 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?

    Reply
  • trim() function should remove any space and not only those on left or on right of the string. this is not trim().

    Reply
    • 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).

      Reply
  • 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 ‘)

    Reply
  • hi my friend
    the answer is so simple
    TRIM FUNCTION
    ..
    ..
    ..
    return replace(@S,’ ‘,”)

    Reply
  • Hi blog.sqlauthority.com,

    what’s the return type when function returns nothing.

    Reply
  • 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.

    Reply
  • 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

    Reply
  • 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

    Reply
  • 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

    Reply
    • 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

      Reply
  • 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

    Reply
  • 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?

    Reply
  • 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?

    Reply
    • 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.

      Reply
    • 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.

      Reply
  • 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.

    Reply
  • 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!)

    Reply

Leave a Reply

Menu