none
LTRIM and RTRIM does not remove spaces.

    Question

  • Hi ,

    I'm not sure about why I'm not able to remove spaces even after trimming them. Can any one plz help me out.  below is the result of query I'm usning.

    select distinct LTRIM(RTRIM(Promotion_Code)) Promotion_Code
    --, count(Promotion_code)
    from dbo.Marketing_Promotion_Tb
    where Promotion_code  like '%1BTPIZZA%'

    Result :

    Promotion_Code   Length

    1BTPIZZA                  10
    1BTPIZZA                   8

    Thanks for help !

    vishal.

    Thursday, October 25, 2012 2:57 PM

Answers

  • Your last characters are CHAR(0). Prior to that in first 3 you have TAB (in last entry only char(0))

    So, you may want to get rid of these characters, e.g.

    SELECT LTRIM(RTRIM(Promotion_Code)) Promotion_Code ,

    convert(binary(15), LTRIM(RTRIM(Promotion_Code)))  Binary, REPLACE(Promotion_Code, CHAR(0),'') as NoCharOCode from dbo.Marketing_Promotion_Tb   where Promotion_code  like '%1BTPIZZA%'



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Thursday, October 25, 2012 4:25 PM
  • Here's a way to remove all characters that don't fit an expected list of characters (tabs, unprintable included)... (retrofit to join to your table instead of my @Demo table).  In your case, you seem to be at risk of more than one special character, so you need something like this.  The post from Narishma through stack overflow is a way to accomplish things, if for example you knew there was always one and only one special/unprintable character.

            Declare @Demo Table (DID int identity, name varchar(99))
            Insert @Demo select 'Al' UNION Select 'Bob$' Union Select '!Carl%' UNION Select '%D#<>?:"{}|\][+_)(*&^%$#@!~`_+?>a*vi()(d&*(&*#< >?:"{}|\][J+o_n)es(*&^%$#@!~`_+?><,./;:"' UNION Select '    Eddie     '
            
            ;With Cleaner (DID, PassNo,  Name, OName) as
            (
                Select DID
                        , 1 as PassNo
                        , Name
                        , Name as OName
                  From @Demo D
                 UNION ALL
                 Select DID
                        , Passno + 1
                        , Cast( Replace(C.Name, SubString(C.Name, CAB.BadPos, 1), '') as VarChar(99)) -- CAN.Name
                        , OName
                  From Cleaner C
                 Cross apply (Select Patindex('%[^ abcdefghijklmnopqrstuvwxyz0123456789-'']%', Name) as BadPos ) as CAB
                 where CAB.badpos > 0
             )
             , Cleaned as
             (
              Select Name, Oname  from Cleaner
               Where  Patindex('%[^abcdefghijklmnopqrstuvwxyz0123456789-'' ]%', Name) = 0
              )
              
            Select D.*, Cleaned.Name as Cleaned_Name, Ltrim(Rtrim(Cleaned.Name)) as Trimmed_and_Cleaned
              from @DEMO D
             Inner join CLEANED on CLEANED.Oname = D.Name

    Returns..

    DID    name    Cleaned_Name    Trimmed_and_Cleaned
    1        Eddie             Eddie         Eddie
    4    Al    Al    Al
    5    Bob$    Bob    Bob
    3    %D#<>?:"{}|\][+_)(*&^%$#@!~`_+?>a*vi()(d&*(&*#< >?:"{}|\][J+o_n)es(*&^%$#@!~`_+?><,./;:"    David Jones    David Jones
    2    !Carl%    Carl    Carl

    Friday, October 26, 2012 2:30 PM

All replies

  • declare @Promotion_Code varchar(50) 
    set @Promotion_Code = '  1BTPIZZA '

    select LEN(@promotion_code),len(LTRIM(RTRIM(@promotion_code)))
    • Proposed as answer by irusul Thursday, October 25, 2012 3:03 PM
    Thursday, October 25, 2012 3:01 PM
  • Most likely the characters are not spaces, but tabs or something else.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Proposed as answer by JR1811 Thursday, October 25, 2012 3:32 PM
    Thursday, October 25, 2012 3:09 PM
  • It may be white space, other than char(32), like char(5).

    Here is how to inspect your string for white space:

    DECLARE @string char(15) = 'New '+char(9)+ 'York '+char(9)+ 'City';
    
    SELECT @string, convert(binary(15), @string);
    -- New 	York 	City	0x4E657720 09 596F726B20 09 43697479

    Data cleansing with REPLACE:

    http://www.sqlusa.com/bestpractices2008/nestedreplace/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Thursday, October 25, 2012 3:09 PM
  • In addition to others comments/answers, I think since you are using an aggregate function / distinct while displaying results and so it's showing 2 records.

    DECLARE @test TABLE(PromCode VARCHAR(25))
    INSERT INTO @test
    SELECT '  1BTPIZZA' UNION ALL
    SELECT '1BTPIZZA     ' UNION ALL
    SELECT '        1BTPIZZA     '
    SELECT DISTINCT LTRIM(RTRIM(PromCode)) AS PromCode
    	,COUNT(PromCode) AS COUNT
    	,LEN(PromCode) AS ActualLen
    	,DATALENGTH(PromCode) AS DataLen
    	,LEN(LTRIM(RTRIM(PromCode))) AS TrimdLen
    FROM @test
    WHERE PromCode LIKE '%1BTPIZZA%'
    GROUP BY PromCode
    SELECT DISTINCT LTRIM(RTRIM(PromCode)) AS PromCode
    FROM @test

    Output :

    PromCode	COUNT	ActualLen	DataLen	TrimdLen
    1BTPIZZA	1	8	13	8
    1BTPIZZA	1	10	10	8
    1BTPIZZA	1	16	21	8
    PromCode
    1BTPIZZA



    Narsimha

    Thursday, October 25, 2012 3:31 PM
  • yes that exactly what I did but It's still not taking out white spaces... plz ref. my above code I'm using
    Thursday, October 25, 2012 3:48 PM
  • This is the result I get when I convert field to binary, Does that mean spaces are tabs and not empty spaces ???

    SELECT LTRIM(RTRIM(Promotion_Code)) Promotion_Code , convert(binary(15), LTRIM(RTRIM(Promotion_Code)))  Binary from dbo.Marketing_Promotion_Tb
     where Promotion_code  like '%1BTPIZZA%'

    Promotion_Code         Binary
    1BTPIZZA           0x31425450495A5A4109090000000000
    1BTPIZZA          0x31425450495A5A4109090000000000
    1BTPIZZA          0x31425450495A5A4109090000000000
    1BTPIZZA         0x31425450495A5A4100000000000000

    Thursday, October 25, 2012 3:54 PM
  • Your last characters are CHAR(0). Prior to that in first 3 you have TAB (in last entry only char(0))

    So, you may want to get rid of these characters, e.g.

    SELECT LTRIM(RTRIM(Promotion_Code)) Promotion_Code ,

    convert(binary(15), LTRIM(RTRIM(Promotion_Code)))  Binary, REPLACE(Promotion_Code, CHAR(0),'') as NoCharOCode from dbo.Marketing_Promotion_Tb   where Promotion_code  like '%1BTPIZZA%'



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Thursday, October 25, 2012 4:25 PM
  • From the below link

    http://stackoverflow.com/questions/3374888/tsql-2008-using-ltrimrtrim-and-still-have-spaces-in-the-data

    DECLARE @test TABLE(PromCode VARCHAR(25))
    INSERT INTO @test
    SELECT '  1BTPIZZA' UNION ALL
    SELECT '1BTPIZZA     ' UNION ALL
    SELECT '        1BTPIZZA     '
    SELECT * FROM @test;
    SELECT --DISTINCT 
    		 LTRIM(RTRIM(
    		(REPLACE(PromCode, 
    		SUBSTRING(PromCode, 
    		PATINDEX('%[^a-zA-Z0-9 '''''']%', PromCode)
    		,1)
    		 , ''
    		)
    		))) AS trimd_promcode
    FROM @test
    WHERE PromCode LIKE '%1BTPIZZA%'


    Narsimha

    Thursday, October 25, 2012 4:31 PM
  • Hi,

    FYI

    http://blog.programmingsolution.net/sql-server-2008/removing-leading-and-trailing-white-space-characters-in-tsql/

    Naomi  N  may have pinned it down correctly ...

    Regards.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help , or you may vote-up a helpful post

    Thursday, October 25, 2012 5:06 PM
  • Here's a way to remove all characters that don't fit an expected list of characters (tabs, unprintable included)... (retrofit to join to your table instead of my @Demo table).  In your case, you seem to be at risk of more than one special character, so you need something like this.  The post from Narishma through stack overflow is a way to accomplish things, if for example you knew there was always one and only one special/unprintable character.

            Declare @Demo Table (DID int identity, name varchar(99))
            Insert @Demo select 'Al' UNION Select 'Bob$' Union Select '!Carl%' UNION Select '%D#<>?:"{}|\][+_)(*&^%$#@!~`_+?>a*vi()(d&*(&*#< >?:"{}|\][J+o_n)es(*&^%$#@!~`_+?><,./;:"' UNION Select '    Eddie     '
            
            ;With Cleaner (DID, PassNo,  Name, OName) as
            (
                Select DID
                        , 1 as PassNo
                        , Name
                        , Name as OName
                  From @Demo D
                 UNION ALL
                 Select DID
                        , Passno + 1
                        , Cast( Replace(C.Name, SubString(C.Name, CAB.BadPos, 1), '') as VarChar(99)) -- CAN.Name
                        , OName
                  From Cleaner C
                 Cross apply (Select Patindex('%[^ abcdefghijklmnopqrstuvwxyz0123456789-'']%', Name) as BadPos ) as CAB
                 where CAB.badpos > 0
             )
             , Cleaned as
             (
              Select Name, Oname  from Cleaner
               Where  Patindex('%[^abcdefghijklmnopqrstuvwxyz0123456789-'' ]%', Name) = 0
              )
              
            Select D.*, Cleaned.Name as Cleaned_Name, Ltrim(Rtrim(Cleaned.Name)) as Trimmed_and_Cleaned
              from @DEMO D
             Inner join CLEANED on CLEANED.Oname = D.Name

    Returns..

    DID    name    Cleaned_Name    Trimmed_and_Cleaned
    1        Eddie             Eddie         Eddie
    4    Al    Al    Al
    5    Bob$    Bob    Bob
    3    %D#<>?:"{}|\][+_)(*&^%$#@!~`_+?>a*vi()(d&*(&*#< >?:"{}|\][J+o_n)es(*&^%$#@!~`_+?><,./;:"    David Jones    David Jones
    2    !Carl%    Carl    Carl

    Friday, October 26, 2012 2:30 PM