Confusion with int

Question

Confusion with int

Anam Verma

SSCertifiable

Points: 5365
More actions
January 7, 2009 at 4:35 pm

#129033

Can't find any explanation behind it. Can anybody please explain me why the results of two select statements are same. I wasn't expecting same results from both of these queries.
CREATE TABLE #tmp (testcol int)
INSERT INTO #tmp values (0)
GO 10
INSERT INTO #tmp values (1)
SELECT * from #tmp
WHERE testcol = ''
SELECT * from #tmp
WHERE testcol = 0
DROP TABLE #tmp

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

anam (1/7/2009)
Can't find any explanation behind it. Can anybody please explain me why the results of two select statements are same. I wasn't expecting same results from both of these queries.
CREATE TABLE #tmp (testcol int)
INSERT INTO #tmp values (0)
GO 10
INSERT INTO #tmp values (1)
SELECT * from #tmp
WHERE testcol = ''
SELECT * from #tmp
WHERE testcol = 0
DROP TABLE #tmp
That is because the empty string ('') casts to 0. Try this: select cast('' as int);

Dugi SSCoach Points: 17998 More actions · Answer 2

anam (1/7/2009)
Can't find any explanation behind it. Can anybody please explain me why the results of two select statements are same. I wasn't expecting same results from both of these queries.
CREATE TABLE #tmp (testcol int)
INSERT INTO #tmp values (0)
GO 10
INSERT INTO #tmp values (1)
SELECT * from #tmp
WHERE testcol = ''
SELECT * from #tmp
WHERE testcol = 0
DROP TABLE #tmp

Interesting ... but I just do the test to be sure that you have right result and from the test it seems thet the value "0" is equaly to = ''

Test:

DECLARE @VAL1 INT

SET @VAL1 = ''

DECLARE @VAL2 INT

SET @VAL2 = 0

IF @VAL1 = @VAL2

PRINT 1

ELSE

PRINT 0

Maybe the others have better explanation!

😎

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Dugi SSCoach Points: 17998 More actions · Answer 3

Lynn Pettis (1/7/2009)
anam (1/7/2009)
Can't find any explanation behind it. Can anybody please explain me why the results of two select statements are same. I wasn't expecting same results from both of these queries.
CREATE TABLE #tmp (testcol int)
INSERT INTO #tmp values (0)
GO 10
INSERT INTO #tmp values (1)
SELECT * from #tmp
WHERE testcol = ''
SELECT * from #tmp
WHERE testcol = 0
DROP TABLE #tmp
That is because the empty string ('') casts to 0. Try this: select cast('' as int);

yea, nice explanation Pettis

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Anam Verma SSCertifiable Points: 5365 More actions · Answer 4

Thanks Lynn.

I think i need another coffee to get me working. i could have figured this out earlier before posting it here.

Cheers