Trailing Spaces in Where Clause

Question

Post reply

Trailing Spaces in Where Clause

David Jackson

SSCertifiable

Points: 6414
More actions
August 10, 2006 at 10:59 am

#172063

Can anyone please explain wht the h*ll is going on here...
if Object_Id('tempdb..#temp') > 0 drop table #temp
Create Table #Temp(id int identity(1,1), Depot varchar(30) not Null, Region varchar(30) not Null)
Set NoCount On
insert into #Temp Values('Glossop', 'Derbyshire')
insert into #Temp Values('Liverpool', 'Mersyside ')
insert into #Temp Values(' Manchester', 'Greater Manchester')
insert into #Temp Values(' Nelson ', ' Lancashire ')
Select replace(Depot,' ', '.') from #Temp
where Depot = 'Glossop' --returns 1 row
Select replace(Depot,' ', '.') from #Temp
where Depot = 'Glossop ' --returns 1 row
Select replace(Depot,' ', '.') from #Temp
where Depot = 'Manchester' --returns 0 rows
Select replace(Depot,' ', '.') from #Temp
where Depot = ' Manchester' --returns 1 row
Select replace(Region,' ', '.') from #Temp
where Region = 'Mersyside' --returns 1 row
Select replace(Region,' ', '.') from #Temp
where Region = 'Mersyside ' --returns 1 row
Select replace(Region,' ', '.') from #Temp
where Region = 'Greater Manchester' --returns 1 row
Select replace(Region,' ', '.') from #Temp
where Region = 'Greater Manchester ' --returns 1 row
Select replace(Region,' ', '.') from #Temp
where Region = 'Lancashire' --returns 0 row
Select replace(Region,' ', '.') from #Temp
where Region = ' Lancashire' --returns 1 row NOT Trimmed!
I would have expected query 2 to return 0 rows, but it finds a row! How?
Dave (Baffled) J
http://glossopian.co.uk/
"I don't know what I don't know."

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Bhushan Default port Points: 1427 More actions · Answer 1

Check the BOL on SET ANSI_PADDING

PRINT 'Testing with ANSI_PADDING ON'

SET ANSI_PADDING ON;

GO

CREATE TABLE t1 (

charcol CHAR(16) NULL,

varcharcol VARCHAR(16) NULL,

varbinarycol VARBINARY(8)

);

GO

INSERT INTO t1 VALUES ('No blanks', 'No blanks', 0x00ee);

INSERT INTO t1 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00);

SELECT 'CHAR' = '>' + charcol + '' + varcharcol + '' + charcol + '' + varcharcol + '<',

varbinarycol

FROM t2;

GO

DROP TABLE t1

DROP TABLE t2

David Jackson SSCertifiable Points: 6414 More actions · Answer 2

Thanks for the input, but just found this.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q316626 which states

The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them.

It specifically mentions the following about ANSI_PADDING

The SET ANSI_PADDING setting does not affect whether SQL Server pads strings before it compares them. SET ANSI_PADDING only affects whether trailing blanks are trimmed from values being inserted into a table, so it affects storage but not comparisons.

So found it, but thanks anyway

Dave J

http://glossopian.co.uk/
"I don't know what I don't know."