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

• Lynn Pettis

  SSC Guru

  Points: 442447

  More actions

  January 7, 2009 at 4:50 pm

  #922999

  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

  January 7, 2009 at 4:54 pm

  #923001

  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

  January 7, 2009 at 4:56 pm

  #923004

  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

  January 7, 2009 at 5:26 pm

  #923011

  Thanks Lynn.

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

  Cheers