How to emulate a boolean with MSSQL
- Microsoft SQL Server does not support the following: where Boolean_Column=true or where Boolean_Column=false.
- The closest approximation is to create a column, assign it a bit datatype, then check for 1 for True or 0 for False.
- The problem that, when upgrading/converting from other databases then, we have values like: T or t or True or true or 1 for true and F or f or False or false or 0. Now how do we deal with all these variation so we can clean it up.
Applies to:
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
Data used
- USE sql911;
- go
- SELECT sales_date,sales_qty,sales_price,logical
- FROM sales02;
- go
sales_date sales_qty sales_price logical ----------------------- --------------------------------------- --------------------------------------- ------- 2005-10-06 10:01:01.000 10.00 11.75 true 2006-10-06 10:01:01.000 10.00 11.75 1 2005-11-06 10:01:01.000 9.00 11.75 0 2006-10-06 10:01:01.000 10.00 11.75 t 2005-12-06 11:01:01.000 8.00 11.75 T 2006-02-06 13:01:01.000 6.00 11.75 false 2006-05-06 16:01:01.000 3.00 11.75 F 2004-10-06 10:01:01.000 10.00 11.75 0 2005-11-06 10:01:01.000 9.00 11.75 False 2006-06-06 17:01:01.000 2.00 11.75 True 2006-01-06 12:01:01.000 7.00 11.75 Yesw 2006-02-06 13:01:01.000 6.00 11.75 Y 2005-03-06 14:01:01.000 5.00 11.75 yes 2006-10-06 10:01:01.000 10.00 11.75 No 2006-10-06 10:01:01.000 3.00 11.75 N 2006-10-06 10:01:01.000 10.00 11.75 n 2004-03-06 14:01:01.000 5.00 11.75 -1 2005-04-06 15:01:01.000 4.00 11.75 NULL 2005-05-06 16:01:01.000 3.00 11.75 NULL 2006-07-06 19:01:01.000 1.00 11.75 1 2006-03-06 14:01:01.000 5.00 11.75 NULL 2006-04-06 15:01:01.000 4.00 11.75 NULL 2006-06-06 17:01:01.000 2.00 11.75 NULL 2007-05-07 11:30:00.000 2.00 10.50 NULL 2007-05-08 13:30:00.000 1.00 10.00 NULL 2006-07-06 19:01:01.000 1.00 11.75 NULL 2007-05-09 14:00:00.000 1.25 10.00 2 2007-11-06 10:01:01.000 9.00 11.75 NULL 2007-05-10 14:00:00.000 5.00 10.00 T 2007-05-11 14:15:00.000 4.00 10.00 F 2007-05-12 15:00:00.000 2.00 10.00 false 2007-05-13 16:00:00.000 1.00 10.00 0 (32 row(s) affected)
Boolean values
- SELECT sales_date,sales_qty,sales_price,logical,
- CASE WHEN CHARINDEX(LEFT(UPPER(logical), 1), 'T1Y') > 0
- THEN 'True'
- ELSE 'False'
- END AS 'Boolean'
- FROM sales02;
sales_date sales_qty sales_price logical Boolean ----------------------- --------------------------------------- --------------------------------------- ------- ------- 2005-10-06 10:01:01.000 10.00 11.75 true True 2006-10-06 10:01:01.000 10.00 11.75 1 True 2005-11-06 10:01:01.000 9.00 11.75 0 False 2006-10-06 10:01:01.000 10.00 11.75 t True 2005-12-06 11:01:01.000 8.00 11.75 T True 2006-02-06 13:01:01.000 6.00 11.75 false False 2006-05-06 16:01:01.000 3.00 11.75 F False 2004-10-06 10:01:01.000 10.00 11.75 0 False 2005-11-06 10:01:01.000 9.00 11.75 False False 2006-06-06 17:01:01.000 2.00 11.75 True True 2006-01-06 12:01:01.000 7.00 11.75 Yesw True 2006-02-06 13:01:01.000 6.00 11.75 Y True 2005-03-06 14:01:01.000 5.00 11.75 yes True 2006-10-06 10:01:01.000 10.00 11.75 No False 2006-10-06 10:01:01.000 3.00 11.75 N False 2006-10-06 10:01:01.000 10.00 11.75 n False 2004-03-06 14:01:01.000 5.00 11.75 -1 False 2005-04-06 15:01:01.000 4.00 11.75 NULL False 2005-05-06 16:01:01.000 3.00 11.75 NULL False 2006-07-06 19:01:01.000 1.00 11.75 1 True 2006-03-06 14:01:01.000 5.00 11.75 NULL False 2006-04-06 15:01:01.000 4.00 11.75 NULL False 2006-06-06 17:01:01.000 2.00 11.75 NULL False 2007-05-07 11:30:00.000 2.00 10.50 NULL False 2007-05-08 13:30:00.000 1.00 10.00 NULL False 2006-07-06 19:01:01.000 1.00 11.75 NULL False 2007-05-09 14:00:00.000 1.25 10.00 2 False 2007-11-06 10:01:01.000 9.00 11.75 NULL False 2007-05-10 14:00:00.000 5.00 10.00 T True 2007-05-11 14:15:00.000 4.00 10.00 F False 2007-05-12 15:00:00.000 2.00 10.00 false False 2007-05-13 16:00:00.000 1.00 10.00 0 False (32 row(s) affected)
There are a few assumptions:
- If it is not defined as true, then it must be false. This means that a NULL will mean False or an abc will also mean False.
- I only look at the 1st uppered [just created a brand new word] character: Line 2: case when charindex(left(upper(logical), 1), 'T1Y') > 0: If the 1st uppered character is a: T or 1 or Y, then it's true.
- You can do the same thing to update or to convert the boolean column to 1 or 0 with:
- UPDATE sales02
- SET logical = CASE WHEN CHARINDEX(LEFT(UPPER(logical), 1), 'T1Y') > 0
- THEN 1
- ELSE 0
- END