Debugging SQL

zlezj

I found this subtlety while debugging some SQL Server code. The first SELECT statement yields 2 rows, the second only 1, the third 2. Frankly: I expected the first two resultsets to be the same...

CREATE TABLE test (
value NVARCHAR(10)
)
GO

INSERT INTO test(value) VALUES('0000001');
INSERT INTO test(value) VALUES('0000001 ');
GO

SELECT * FROM test WHERE value='0000001'
SELECT * FROM test WHERE value LIKE '%1'
SELECT * FROM test WHERE value LIKE '%1%'
GO

DROP TABLE test
GO

PIEBALDconsult

Of course not.

zlezj

I usually don't use the like operator and I always trim my strings before storing them in a database, so I never ran into this. If this scenario was in a developers-quiz, I wonder: how many developers would answers this question correct?

PIEBALDconsult

zlezj wrote:

how many developers would answers this question correct?

Any who are worthwhile.

Jeremy Tierman

Why don't you explain why? If you change from NVARCHAR to VARCHAR, each recordset is 2. :zzz:

Lost User

I'm also interested in reason behind that sort of behavior. I just don't use SQL that much to figure it out myself ;)

Lost User

According to this, behavior is expected ;)

Paulo Zemek

To be honest, I really don't understand why the = (equal) operator returns a value that is not equal.

Lost User

Here is the answer to that one ;)

Jeremy Tierman

The key is with unicode data. From LIKE..."When you use Unicode data (nchar or nvarchar data types) with LIKE, trailing blanks are significant; however, for non-Unicode data, trailing blanks are not significant. ..." This was interesting since I haven't done much with Unicode, but in my quest I can't figure out one result. In the following, I turned off ansi padding and inserted into a char and a varchar data with trailing spaces. The space taken for each column is the same since it was truncated on insert. Why does comparing the char (col1) LIKE N'% King' return 0 rows, but comparing the varchar (col1) LIKE N'% King' return 2 rows???

create table #resultsRec ( padding bit, col varchar(20), opr varchar(5), prm varchar(20), rec int)
create table #resultsSz ( padding bit, col varchar(20), sz int, dsz int)

set ansi_padding off
create table #t (id int identity(1,1), col1 char(30), col2 varchar(30));

insert #t (col1, col2) values ('Robert King', 'Robert King');
insert #t (col1, col2) values ('Robert King ', 'Robert King ');

insert #resultsSz select 1, 'char', len(col1), datalength(col1) from #t
insert #resultsSz select 1, 'varchar', len(col2), datalength(col2) from #t

insert #resultsRec select 0, 'char', '=', '''Robert King''', count(*) FROM #t WHERE col1 = 'Robert King' -- returns 2 rows (?) must be trimmed
insert #resultsRec select 0, 'char', '=', '''Robert King ''', count(*) FROM #t WHERE col1 = 'Robert King ' -- returns 2 rows (?) must be trimmed
insert #resultsRec select 0, 'char', '=', 'N''Robert King''', count(*) FROM #t WHERE col1 = N'Robert King' -- returns 2 rows (?) must be trimmed
insert #resultsRec select 0, 'char', '=', 'N''Robert King ''', count(*) FROM #t WHERE col1 = N'Robert King ' -- returns 2 rows (?) must be trimmed
insert #resultsRec select 0, 'char', 'LIKE', '''% King''', count(*) FROM #t WHERE col1 LIKE '% King' -- returns 2 rows, trailing spaces not signficant with ASCII LIKE
insert #resultsRec select 0, 'char', 'LIKE', '''% King ''', count(*) FROM #t WHERE col1 LIKE '% King ' -- returns 2 rows, trailing spaces not signficant with ASCII LIKE
insert #resultsRec select 0, 'char', 'LIKE', 'N''% King''', count(*) FROM #t WHERE col1 LIKE N'% King' -- returns 0 rows (?), trailing spaces signficant with UNICODE LIKE
insert #resultsRec select 0, 'char', 'LIKE', 'N''% King ''', count(*) FROM #t WHERE col1 LIKE N'% King ' -- returns 0 rows, trailing spaces signficant with UNICODE LIKE

insert #resultsRec select 0,

Brady Kelly

Those who see the space.

You really gotta try harder to keep up with everyone that's not on the short bus with you. - John Simmons / outlaw programmer.

Bernhard Hiller

About a decade ago when working with an access database, I found a similar strange behavior. The select statements Select * From Table1, Table2 Where Table1.FieldA = Table2.FieldB and Select * From Table1 Inner Join Table2 On Table1.FieldA = Table2.FieldB gave different results caused by such trailing spaces. I found that in a database for planning the equipment of a hospital, and it seems to me that the guys of the NHS used this difference to obfuscate the data, so that the database could only be used with their software.

kmanuraj

note that the second insert statement INSERT INTO test(value) VALUES('0000001'); INSERT INTO test(value) VALUES('0000001 '); where there is a space left after '0000001' ('0000001space')

zlezj

Yep, that was causing the problems

Harry Ridpath

The big difference here is "Implicite" and "Explicite" Conversion. The first return is an implicite conversion of the arguments. Both values will convert to a number value and compare. The second return is an explicite conversion and the arguments are converted to VARCHAR or NVARCAHR and compared. The third return is explicite but does not care about anything from the second % SIGN on.

RedSpear