Float Field Evalutes to Both 0 '0' and Empty

• Robert O'Byrne

  SSCrazy

  Points: 2017

  More actions

  March 31, 2008 at 8:25 am

  #204837

  I was playing around with a test import table and was surprised with the following query results.

  (PS Don't get sidetracked on the fact that Im using a FLOAT to store a rate. I'm just playing around with the data.)

  Background: I set up a quickie test table with a float field called tRate designed with a default value of 0 (no decimal, just 0)

  I imported 500 records. 400 records have a proper value in the tRate field while 100 records have NULL in the tRate field. Sure enough the 400 records contain their imported value and 100 records now have 0 as a value in tRate

  I then ran the following queries with different WHERE clauses

  SELECT * FROM ImportTest WHERE tRate IS NULL

  Result: Returned no records as expected

  SELECT * FROM ImportTest WHERE tRate = 0

  Result: Returned 100 records? I am surprised to see a float actual EQUAL a 0, but i guess since its being entered as a default value it works.

  SELECT * FROM ImportTest WHERE tRate = '0'

  Result: Returned the same 100 records? I guess if the =0 test works, then the inherent character covnersion would work as well?

  SELECT * FROM ImportTest WHERE tRate = ''

  Result: Returned the same 100 records??? This one threw me for a loop?

  How is could this test evaulating to TRUE for both 0 '0' and ''

  I know floats are approximations of data, but how this conversion is taking place?

• Michael Earl-395764

  SSC Guru

  Points: 53873

  More actions

  March 31, 2008 at 9:15 am

  #795997

  Empty string resolves to zero when converted to FLOAT or INT. Try this:

  SELECT CONVERT(FLOAT,'') AS EmptyFloat, CONVERT(INT,'') AS EmptyInt

  This is by design - I cannot remember why.

  I'm sure someone else can post a reason.

• Robert O'Byrne

  SSCrazy

  Points: 2017

  More actions

  March 31, 2008 at 9:29 am

  #796006

  Thanks Michael! That answers alot. I was just suprised to see this conversion action taking place during a where clause, but it at least it makes some sense now.

  I dig a little into BOL/google to get the background on it.