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.

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

Most likely the characters are not spaces, but tabs or something else.

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/

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

yes that exactly what I did but It's still not taking out white spaces... plz ref. my above code I'm using

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

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%'

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%'

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.

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

• Proposed as answer by Naomi NModerator Friday, October 26, 2012 4:04 PM
• Marked as answer by Iric WenModerator Friday, November 02, 2012 9:15 AM

That worked, Learned something new Char(0) to replace and trim.

Thanks