''SMITH'' does NOT equal ''SMITH ''

Question

Post reply

''SMITH'' does NOT equal ''SMITH ''

Express12

SSCoach

Points: 18514
More actions
August 15, 2006 at 11:56 am

#113855

SELECT * from TAB_A WHERE COL_1='SMITH'
SELECT * from TAB_A WHERE COL_1='SMITH     ' --- note the 5 spaces after COL_1 name value 'Smith'
The column values 'SMITH' and 'SMITH     ' are both returning the same set of 3 qualified rows. (I checked the PK values)
Is there a SQL setting I am missing here?   ANSI_NULLS ??   If so, what command can I use to alleviate the duplicate result sets?   'SMITH' does NOT equal 'SMITH     '
thx in advance

BT

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

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

It's due to ANSI SQL standards.

Does this help:

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

You can work around it using LIKE:

SELECT * from TAB_A WHERE COL_1 LIKE 'SMITH'

SELECT * from TAB_A WHERE COL_1 LIKE 'SMITH ' --- note the 5 spaces after COL_1 name value 'Smith'

Jasmine D. Adamson Hall of Fame Points: 3428 More actions · Answer 2

if you do not know the value ahead of time, you can make sure that things don't have trailing spaces by comparing the datalength.

where col1 = col2 and datalength(col1) = datalength(col2)

Changing the ANSI settings for your stored procedure will eliminate the 'non-consideration of trailing whitespace' problem, but if you can't change that, you will have to use the datalength hack.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Why do you have trailing spaces and what is it that you're trying to do? Are you trying to find entries that have trailing spaces to eliminate the trailing spaces or ????

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