March 31, 2008 at 8:25 am
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?
March 31, 2008 at 9:15 am
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.
March 31, 2008 at 9:29 am
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply