ANSI_PADDING affects storage but not QUERIES <OR> Trailing spaces ignored in WHERE clause

Question

Post reply

ANSI_PADDING affects storage but not QUERIES <OR> Trailing spaces ignored in WHERE clause

Jason Selburg

SSC-Insane

Points: 24641
More actions
November 28, 2011 at 1:46 pm

#248734

While answer another related question in the forums, I cam across this previously unknown (to me) tidbit. And being that this is a place for everyone to learn, I thought I'd throw this up for everyone to answer/see.
As ANSI_PADDING affects the storage of the data, I thought it would also have similar affects to comparisons as well, but it seems not.
As shown below, one would expect
- a query with WHERE someCol = "123_" (where the underscore is a space) would only return the column that also contains the trailing space if ANSI_PADDING were set to ON
- similarly the query with WHERE someCol = "123" would NOT return one with a value of "123_"
The code below (partially stolen from BOL) although lengthly and slightly rough to follow illustrates the question.
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);
--- see how the data is stored
SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t1;
GO
-- now use in where clauses
SELECT 'should be nothing - VARCHAR = >No blanks <', 'VARCHAR'='>' + varcharcol + '<'
FROM t1 WHERE varcharcol = 'No blanks '
SELECT 'should be nothing - CHAR = >No blanks<', 'CHAR' = '>' + charcol + '<'
FROM t1 WHERE charcol = 'No blanks'
SELECT 'should be nothing - VARCHAR = >Trailing blank <', 'VARCHAR'='>' + varcharcol + '<'
FROM t1 WHERE varcharcol = 'Trailing blank'
SELECT 'should be nothing - CHAR = >Trailing blank<', 'CHAR' = '>' + charcol + '<'
FROM t1 WHERE charcol = 'Trailing blank '
GO
PRINT 'Testing with ANSI_PADDING OFF';
SET ANSI_PADDING OFF;
GO
CREATE TABLE t2 (
charcol CHAR(16) NULL,
varcharcol VARCHAR(16) NULL,
varbinarycol VARBINARY(8)
);
GO
INSERT INTO t2 VALUES ('No blanks', 'No blanks', 0x00ee);
INSERT INTO t2 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00);
--- see how the data is stored
SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t2;
-- now use in where clauses
SELECT 'should be nothing - VARCHAR = >No blanks <', 'VARCHAR'='>' + varcharcol + '<'
FROM t2 WHERE varcharcol = 'No blanks '
SELECT 'should be nothing - CHAR = >No blanks<', 'CHAR' = '>' + charcol + '<'
FROM t2 WHERE charcol = 'No blanks'
SELECT 'should be nothing - VARCHAR = >Trailing blank <', 'VARCHAR'='>' + varcharcol + '<'
FROM t2 WHERE varcharcol = 'Trailing blank'
SELECT 'should be nothing - CHAR = >Trailing blank<', 'CHAR' = '>' + charcol + '<'
FROM t2 WHERE charcol = 'Trailing blank'
GO
DROP TABLE t1
DROP TABLE t2
So it seems that SQL ignores the trailing spaces during the WHERE clause.
Can someone please shed some light on this?
Is this a known issue (please post links), my ignorance showing through (no link required), or something else entirely.
*** I do not pretend to know everything about SQL, even the seemingly minor things. :hehe: ***
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg

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

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

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

Sorry, I think I had a minor issue with the code. This illustrates my question more appropriately.

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);

--- see how the data is stored

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

varbinarycol

FROM t1;

GO

-- now use in where clauses

SELECT 'should be nothing - VARCHAR = >No blanks <', 'VARCHAR'='>' + varcharcol + '<'

FROM t1 WHERE varcharcol = 'No blanks '

SELECT 'should be nothing - CHAR = >No blanks<', 'CHAR' = '>' + charcol + '<'

FROM t1 WHERE charcol = 'No blanks'

SELECT 'should be nothing - VARCHAR = >Trailing blank <', 'VARCHAR'='>' + varcharcol + '<'

FROM t1 WHERE varcharcol = 'Trailing blank'

SELECT 'should be nothing - CHAR = >Trailing blank<', 'CHAR' = '>' + charcol + '<'

FROM t1 WHERE charcol = 'Trailing blank'

GO

PRINT 'Testing with ANSI_PADDING OFF';

SET ANSI_PADDING OFF;

GO

CREATE TABLE t2 (

charcol CHAR(16) NULL,

varcharcol VARCHAR(16) NULL,

varbinarycol VARBINARY(8)

);

GO

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

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

--- see how the data is stored

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

varbinarycol

FROM t2;

-- now use in where clauses

SELECT 'should be nothing - VARCHAR = >No blanks <', 'VARCHAR'='>' + varcharcol + '<'

FROM t2 WHERE varcharcol = 'No blanks '

SELECT 'should be nothing - CHAR = >No blanks<', 'CHAR' = '>' + charcol + '<'

FROM t2 WHERE charcol = 'No blanks'

SELECT 'should be nothing - VARCHAR = >Trailing blank<', 'VARCHAR'='>' + varcharcol + '<'

FROM t2 WHERE varcharcol = 'Trailing blank '

SELECT 'should be nothing - CHAR = >Trailing blank<', 'CHAR' = '>' + charcol + '<'

FROM t2 WHERE charcol = 'Trailing blank '

GO

DROP TABLE t1

DROP TABLE t2

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Jeff Moden SSC Guru Points: 1003895 More actions · Answer 2

As ANSI_PADDING affects the storage of the data, I thought it would also have similar affects to comparisons as well, but it seems not.

Correct. It has no affect on the kind of comparisons you've done. Trailing spaces are not considered in the straight-up comparisons. As you know, they're not considered with LEN() but are considered with DATALENGTH(). I'm just telling you that so you don't think it's an anomoly. 🙂

Stop and think about how useless CHAR columns would actually be if you had to account for trailing spaces in WHERE clause expressions.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)