SQL SERVER – TRIM() Function – UDF TRIM()

SQL Server does not have function which can trim leading or trailing spaces of any string. TRIM() is very popular function in many languages. SQL does have LTRIM() and RTRIM() which can trim leading and trailing spaces respectively. I was expecting SQL Server 2005 to have TRIM() function. Unfortunately, SQL Server 2005 does not have that either. I have created very simple UDF which does the same work.

FOR SQL SERVER 2000:
CREATE FUNCTION dbo.TRIM(@string VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
RETURN
LTRIM(RTRIM(@string))
END
GO

FOR SQL SERVER 2005:
CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
RETURN
LTRIM(RTRIM(@string))
END
GO

Both the above UDF can be tested with following script
SELECT dbo.TRIM(' leading trailing ')
It will return string in result window as
'leading trailing'

Here is the quick video on the same subject:

There will be no spaces around them. It is very little but useful trick.

Reference : Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Six Properties of Relational Tables
Next Post
SQL SERVER – 2005 Take Off Line or Detach Database

Related Posts

127 Comments. Leave new

  • H.Phaninder Reddy
    August 3, 2011 11:38 am

    How can i remove middle characters from a string?
    eg: phaninder,reddy is a name……..i would like to remove ‘,’ from this name using trim function..pls give me the query…..

    Reply
    • You cannot use trim function to remove a comma. Use replace function

      select replace(col,’,’,”) from table

      Reply
  • I have to bind a nvarchar column to sql reporting services, and need to format it, if it has space between the 2 lines. Kindly help me how to do it?

    Reply
  • Hemant Ramteke
    November 4, 2011 5:00 pm

    select LTRIM(RTRIM(columnname)) as outputcolumn from tablename

    Reply
  • Hemant Ramteke
    November 4, 2011 5:02 pm

    In sql server TRIM function not found to trim data , but it provides LTRIM for left trim & RTRIM for right trim .

    Reply
  • Abdul Awwal Chaudhary
    December 7, 2011 2:16 pm

    If I use
    SELECT *
    FROM Employee
    Where LTRIM(RTRIM((empcode)) = ‘E123’

    It return the result but if I omit the LTRIM and RTRIM , it shows no record found.
    How to get the result without using LTRIM and RTRIM

    Reply
  • Hi,
    I am using SQL 2008, and am trying to insert value from table 1 to table 2 with multi records from table1 column “Description” and would like to populate in 1 unique record into table 2.

    I have given an example below;

    TABLE 1

    PATIENT_ID DESCCRIPTION

    2771 MOTRIN TAB
    2771 TYLENOLTAB
    2771 ZANTAC TAB

    2775 ABC TAB
    2775 123 TAB
    2775 XYZ TAB

    Example: Copy values from table 1 into table 2 with single row;

    TABLE 2

    PATIENT_ID DESCRIPTION

    2771 MOTRIN TAB,TYLENOL TAB,ZANTAC TAB
    2775 ABC TAB, 123 TAB, XYZ TAB

    Thanks

    Reply
  • i need replace Single Quotes :
    my input is : who’s,this,book’s
    i want the output is : whos
    this
    books

    Reply
    • Anjibabu Kamma
      May 6, 2013 5:26 pm

      SELECT REPLACE (REPLACE (‘who’s,this,book’s’,’’’,”),’,’,’ ‘)

      Reply
  • I am suffering from a problem. there is one database field that is entered by users. User may some time entered lot of spaces between characters. This problems in crystal report. few line print on one page and other lines print on 2nd and 3rd page. I checked but this issue not belong to crystal report formatting. I search a lot for crystal report formulas to get solution but i can’t.
    I want to remove empty lines between this string. how can i do it?

    Thanks

    Reply
  • Harish Panguluri
    May 29, 2012 3:17 pm

    Hi, Can we use this one to the entire column of the table?
    something like ”Select Trim([Column Name]);”

    Thanks

    Reply
  • Parth Malhan
    July 5, 2012 4:25 pm

    HI,

    Is there something in SQL Server to make Ltrim as
    default Constraint or policy in Database
    For All Tables

    eg.

    SELECT ‘ a ‘ A INTO TestTable

    And on select Statement, This Table return only ‘a’ without Leading\Trailing Spaces.

    Reply
  • Thank you for your post!

    I was looking for a function to turn any number of consecutive spaces into a single space, for example ‘I love Pinal Dave’ -> ‘I love Pinal Dave’. I came up with…

    CREATE FUNCTION dbo.SPACEOUT(@string VARCHAR(255))
    RETURNS VARCHAR(255)
    BEGIN
    DECLARE @doublespaceindex TINYINT,
    @tempstring VARCHAR(255)
    SET @doublespaceindex = CHARINDEX(‘ ‘,@string)
    WHILE @doublespaceindex > 0
    BEGIN
    SET @string = REPLACE(@string,’ ‘,’ ‘)
    SET @doublespaceindex = CHARINDEX(‘ ‘,@string)
    END
    RETURN @string
    END

    Reply
  • hi
    how can i select data that contains in [ ] in sql column.
    e.g.i have a column that contains large data in that col i have some data as –
    employee name is [emp_name] and job description is [job_desc].
    and i want to select only [emp_name] using sql qry n display it in stringbuilder.
    thanks

    Reply
  • Hello, my doubt is can we use ltrim/rtrim on integers and date variables.
    this works pretty well, but certain coding standards avoid the use of trimming integer or date variables. Can you please suggest why so?

    Reply
  • hi,
    i have a problem, want to retrieve & from table in asp.net page .
    but the result is & is display in textbox please sort out this problem

    Reply
  • Subhajit Maji
    July 29, 2013 5:58 pm

    Hey wat about the spaces which are between some characters?

    Reply
  • Carlos Mandujano
    January 22, 2014 12:13 am

    Magnifica aportacion … GRACIAS !!!

    Reply
  • How to remove spaces in themiddle of the function while inserting the data into mysql

    Reply
  • Eduardo Cuomo
    March 5, 2015 11:15 pm

    My Function:

    CREATE FUNCTION StrTrim(@Str VARCHAR(MAX)) RETURNS VARCHAR(MAX) BEGIN
    DECLARE @NewStr VARCHAR(MAX) = NULL

    IF (@Str IS NOT NULL) BEGIN
    SET @NewStr = ”

    DECLARE @WhiteChars VARCHAR(4) =
    CHAR(13) + CHAR(10) — ENTER
    + CHAR(9) — TAB
    + ‘ ‘ — SPACE

    IF (@Str LIKE (‘%[‘ + @WhiteChars + ‘]%’)) BEGIN

    ;WITH Split(Chr, Pos) AS (
    SELECT
    SUBSTRING(@Str, 1, 1) AS Chr
    , 1 AS Pos
    UNION ALL
    SELECT
    SUBSTRING(@Str, Pos, 1) AS Chr
    , Pos + 1 AS Pos
    FROM Split
    WHERE Pos = (
    SELECT MIN(Pos)
    FROM Split
    WHERE CHARINDEX(Chr, @WhiteChars) = 0
    )
    AND Pos <= (
    SELECT MAX(Pos)
    FROM Split
    WHERE CHARINDEX(Chr, @WhiteChars) = 0
    )
    END
    END

    RETURN @NewStr
    END

    Source:

    Reply
  • Eduardo Cuomo
    May 29, 2015 6:02 pm

    Other script:

    DECLARE @Str NVARCHAR(MAX) = N’
    foo bar
    Foo Bar

    PRINT ‘[‘ + @Str + ‘]’

    DECLARE @StrPrv NVARCHAR(MAX) = N”

    WHILE ((@StrPrv @Str) AND (@Str IS NOT NULL)) BEGIN
    SET @StrPrv = @Str

    — Beginning
    IF EXISTS (SELECT 1 WHERE @Str LIKE ‘[‘ + CHAR(13) + CHAR(10) + CHAR(9) + ‘]%’)
    SET @Str = LTRIM(RIGHT(@Str, LEN(@Str) – 1))

    — Ending
    IF EXISTS (SELECT 1 WHERE @Str LIKE ‘%[‘ + CHAR(13) + CHAR(10) + CHAR(9) + ‘]’)
    SET @Str = RTRIM(LEFT(@Str, LEN(@Str) – 1))
    END

    PRINT ‘[‘ + @Str + ‘]’

    Result:

    [
    foo bar
    Foo Bar

    ]
    [foo bar
    Foo Bar]

    Reply
  • Snigdha Agarwal
    October 9, 2015 11:06 am

    Hi, my query is not related to SQL Server, but to Informix Database. Do you have any idea how efficient is RTRIM method in an SQL query run on an Informix Database, containing about 50,000 rows.

    Reply

Leave a Reply

Menu