Strange SQL Server Evaluation of 0 (bit) = '' (Empty string) or even non empty string

Question

Post reply

Strange SQL Server Evaluation of 0 (bit) = '' (Empty string) or even non empty string

andy.fensham

Old Hand

Points: 351
More actions
September 6, 2011 at 3:06 am

#149583

Hi, I am getting a very strange sql server evaluation.
I have a table with bit columns with nulls allowed or not allowed. If I say : Select * from [MyTable] where Booleancolumn = ''
SQL Server will bring back values where Booleancolumn = 0
Check this :
SELECT CASE WHEN Cast(0 AS bit) = ' ' THEN 1 ELSE 0 END
and
SELECT CASE WHEN 0 = '' THEN 1 ELSE 0 END
Both evaluate to true, which should not be the case.
Please help urgently.

Viewing 12 posts - 1 through 12 (of 12 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

what you are seeing here is the implicit conversion that takes place when comparing different data types.

so in this case an empty string ('') is implicity converted to a 0 so it evaluates as a true.

Also it is worth noting that an empty string does not equal NULL, to do this with your code you will need.

SELECT CASE WHEN Cast(0 AS bit) = NULL THEN 1 ELSE 0 END

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

steveb. (9/6/2011)
... an empty string does not equal NULL, to do this with your code you will need.
SELECT CASE WHEN Cast(0 AS bit) = NULL THEN 1 ELSE 0 END

This is confusing, Steve - what are you trying to say? Try this:

SELECT CASE WHEN Cast(NULL AS bit) = NULL THEN 1 ELSE 0 END

SELECT CASE WHEN Cast(NULL AS bit) IS NULL THEN 1 ELSE 0 END

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

steveb. SSC-Forever Points: 46733 More actions · Answer 3

I had the code backwards, thanks Chris for clearing that up.

andy.fensham Old Hand Points: 351 More actions · Answer 4

Hi Guys,

I am fine with the implicit conversion,

But if I say the following, I should not be getting records back.

Lets say I have a table called MyTable.

Id INT,

Description varchar(10)

BooleanColumn bit

Select * from [MyTable] where BooleanColumns = ''

or even

Select * from [MyTable] where BooleanColumns = ' '

will bring records back if the BooleanColumn value = 0 (false)

Thanks so much for your quick replies

Gail Shaw SSC Guru Points: 1004504 More actions Follow · Answer 5

andy.fensham (9/7/2011)
But if I say the following, I should not be getting records back.

You should be, because of the conversions. CAST(' ' AS BIT) = 0

Lets say I have a table called MyTable.
Id INT,
Description varchar(10)
BooleanColumn bit
Select * from [MyTable] where BooleanColumns = ''
or even
Select * from [MyTable] where BooleanColumns = ' '
will bring records back if the BooleanColumn value = 0 (false)

Yes, it will. Because SQL can't compare a boolean and a string, so it has to convert the string to bit first.

SELECT CAST(' ' AS bit)

That returns 0, so the string, when converted to bit does match rows where the bit column is 0.

SQL has bit columns, they're numeric type columns, not boolean. 0 is not automatically false, it's just 0. If I wanted, I could use 0 for true and 1 for false in an app.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

andy.fensham (9/7/2011)
...
I am fine with the implicit conversion...

The implicit conversion of an empty string to a BIT datatype value 0 is still causing you some confusion - I'd suggest you're not fine with it! There are some datatype conversions which, when performed implicitly, can cause confusion and unintended results. This is one of them. Conversion between string datatypes and date/time datatypes is another. If you are unsure of the consequences on an implicit conversion, then don't rely on it - use an explicit conversion.

DECLARE @MatchVariable VARCHAR(5)

SET @MatchVariable = ''

Select * from [MyTable] where BooleanColumns = CAST(CASE

WHEN @MatchVariable = '1' THEN 1

WHEN @MatchVariable = '0' THEN 0

ELSE NULL END AS BIT)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

paul.knibbs SSCoach Points: 15320 More actions · Answer 7

Out of interest, what are you *expecting* to get back when you compare a Boolean column to an empty string?

steveb. SSC-Forever Points: 46733 More actions · Answer 8

I think the confusion lies in assuming that a bit data type = a boolean data type which is not the case, as SQL server does not handle boolean data types.

andy.fensham Old Hand Points: 351 More actions · Answer 9

I agree with the implicit conversion,

so 0 is equal to empty string

but 0 should not be equal to a string with 10 spaces, so I find the implicit conversion not very consistent.

We have never before compared different data types...we would always explicitly convert...I was just surprised with the results.

Thanks so much for the help everyone,

Andy

Gail Shaw SSC Guru Points: 1004504 More actions Follow · Answer 10

andy.fensham (9/8/2011)
but 0 should not be equal to a string with 10 spaces, so I find the implicit conversion not very consistent.

As far as SQL is concerned, a string with 10 spaces is an empty string. SQL always ignores trailing spaces when converting or comparing strings (ANSI rules). It has to, to be able to sensible compare columns with different length char types.

If it shouldn't be 0, then what should it be? The only thing that converts to 1 is the string '1' (with or without trailing spaces) and a bit has no other possible values, so what should the string with 10 spaces be? A data type conversion error (that's the only option other than 0 or 1)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

andy.fensham Old Hand Points: 351 More actions · Answer 11

andy.fensham

Old Hand

Points: 351

September 8, 2011 at 11:55 pm

#1379003

Valid point....agree

Strange SQL Server Evaluation of 0 (bit) = '' (Empty string) or even non empty string

Cookies on SQLServerCentral