locked
Convert from boolean to bit RRS feed

  • Question

  • Is there a simple way to convert a boolien value to a bit value so that it can be returned?

    I have used "CASE WHEN {boolean expression} THEN 1 ELSE 0 END" but that somehow seems inelegant to me and certainly detracts from readability if there are a lot of such values in a query.

    Jacob
    Monday, July 27, 2009 4:04 PM

Answers

  • I would then say that the "short answer" to your question is "no"; you need to write out your case statements and toggle between 0 and 1 based on the results of your case statements.  Yes, it will make your code more long-winded; it is what it is.


    Kent
    • Marked as answer by Zongqing Li Friday, August 21, 2009 9:24 AM
    Saturday, August 8, 2009 6:28 PM

All replies

  • Is this intended to start a discussion or are you asking for an alternative?
    Monday, July 27, 2009 4:51 PM
  • Is this intended to start a discussion or are you asking for an alternative?

    I am looking for an alternative.

    Jacob
    Monday, July 27, 2009 5:25 PM
  • Yes. And No.

    It depends on the boolean expression. There is no such thing as CBOOL() in T-SQL.

    For simpler boolean expressions, such as CASE WHEN X > 0 THEN
    there is no alternative. Rewriting as SIGN(x) = 1 will gain nothing.



    So is there a way for more complex expressions?
    Monday, July 27, 2009 5:27 PM
  • There are no bits in SQL. Most often, it's temporal data (is_approved vs date_approved)
    Monday, July 27, 2009 6:06 PM
  • in sql server we have bit column that return 1 or 0 only.

    you can use query like this

    select (case when 'TRUE' then 1 else 0 ) boolean result.

    this might helps. I also take a look of alternative. if we have?


    Shamas Saeed MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com
    Tuesday, July 28, 2009 12:05 PM
  • About how many lines is your {boolean expression}?  I am assuming from the post that this expression is long-winded or very long-winded -- is that the case?  It will help if you post what you are dealing with so that we can quit our guessing game.

    Tuesday, July 28, 2009 12:13 PM
  • Is there a simple way to convert a boolien value to a bit value so that it can be returned?

    I have used "CASE WHEN {boolean expression} THEN 1 ELSE 0 END" but that somehow seems inelegant to me and certainly detracts from readability if there are a lot of such values in a query.

    Jacob

    Your CASE expression and similar solutions will convert a {boolean expression} that results in "UNKNOWN" to 0. If you want to return a NULL for the UNKNOWN case then use

    CASE WHEN {boolean expression} THEN 1 WHEN NOT ({boolean expression}) THEN 0 END
    Tuesday, July 28, 2009 12:14 PM
  • Jacob,

    You want the ol' evaluation syntax "(expression)" so you can do something like:

    SELECT (Age > 17) AS [IsLegalAge]
         , LastName
         , FirstName
      FROM Applicant
    GO

    ...producing output...

    IsLegalAge LastName FirstName
    -----------------------------------------------------
    1  Crazy  Adam
    0  Grader  Second

    JET has the IIF() function:

    SELECT IIF(Age > 17, 1, 0) AS [IsLegalAge]
         , LastName
         , LastName
      FROM Applicant
    GO

    However, SQL Server doesn't provide a comparable method (as far as I know). Let me frame that answer. With-in and IF or statment, you can have an expression:

    IF (1=1) PRINT 'Equal';

    However, the expression won't evaluate in a SELECT unless it is in a CASE statement (as you noted).

    SELECT (1=1); --Won't compile

    SQL does not default "1" to boolean TRUE or "0" to boolean FALSE.

    IF (1) PRINT 'Equal';  --Won't compile

    So, an expression that returns 1 or 0 will not return true or false respectively.

    You're stuck with CASE statements; although, you can create your own UDF and call it in a similar fashion as IIF.

    Here is a link to the T-SQL page on expressions. You can examine it for unfound hacks

    http://msdn.microsoft.com/en-us/library/ms190286.aspx

    Cheers,
    Crazy Adam

    Tuesday, July 28, 2009 1:05 PM
  • I don't have a specific piece of code I am working on right now. but, this is an issue I have faced many times and I hope that there is a better way to do it.
    Saturday, August 1, 2009 11:07 PM
  • I would then say that the "short answer" to your question is "no"; you need to write out your case statements and toggle between 0 and 1 based on the results of your case statements.  Yes, it will make your code more long-winded; it is what it is.


    Kent
    • Marked as answer by Zongqing Li Friday, August 21, 2009 9:24 AM
    Saturday, August 8, 2009 6:28 PM
  • It depends on the expression and on what you consider elegant.
    Monday, August 10, 2009 12:43 PM
  • It depends on the expression and on what you consider elegant.

    Yup
    Monday, August 10, 2009 1:32 PM