CODEWITHSUNDEEP
sql-servert-sql
tchock
tchock

Reputation: 245

Set Ansi_Padding - Doesn't appear to make a difference?

I've been reading about differences in char vs varchar, and my understanding is that if ANSI_PADDING is on, I should expect differences when querying. For example, if ANSI_PADDING is on, and I have a column set to char(10), I'm expecting that when I insert into the column let's say 'a', it will pad 9 characters of space. Thus, I've read that I should not be able to query for a char column of 'a', but I'd need to query for 'a_________' (_ for spaces). Anyway, here is an example below that I would not expect to return results, but it does. I can't for the life of me create an example that returns different results based on whether ANSI_PADDING is on/off.

SET ANSI_PADDING ON

DECLARE @table TABLE 
(
 [identity] int identity(1,1)
,[char] char(10) 
,[varchar] varchar(10) 
)


INSERT INTO @table

SELECT 'a', 'a'

SELECT * FROM @table
WHERE [char] = 'a' -- I expect this to not return results...

SELECT * FROM @table
WHERE [varchar] = 'a'

Upvotes: 0

Views: 365

Answers (1)

Chris Steele
Chris Steele

Reputation: 1381

Comparisons ignore trailing spaces in char/varchar data, so your comparison of 'a' will hit any value that differs only in trailing space ('a ', 'a ', etc.). Take a look at this query:

SET ANSI_PADDING ON

create table #moose (
        charval char(10),
        varcharval varchar(10)
    )

insert #moose select 'one', 'one'

select  '"' + charval + '"',
        '"' + varcharval + '"'
    from #moose

drop table #moose

you'll see that the charval is indeed padded with spaces.

Upvotes: 3

Related Questions