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.

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 (http://blog.SQLAuthority.com)

About these ads

21 thoughts on “SQL SERVER – 2008 – TRIM() Function – User Defined Function

  1. 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?

  2. Pingback: SQL SERVER - 2008 - Enhenced TRIM() Function - Remove Trailing Spaces, Leading Spaces, White Space, Tabs, Carriage Returns, Line Feeds Journey to SQL Authority with Pinal Dave

  3. 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?

  4. Pingback: SQL SERVER – Connect Item – Vote for Feature Request Function TRIM Journey to SQL Authority with Pinal Dave

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

  5. 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 ‘)

  6. 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.

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

  8. 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

  9. 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

  10. Pingback: SQL SERVER – TRIM Function to Remove Leading and Trailing Spaces of String – SQL in Sixty Seconds #040 – Video « SQL Server Journey with SQL Authority

  11. 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

  12. 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?

  13. 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?

  14. Pingback: SQL SERVER – Weekly Series – Memory Lane – #050 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s