CODEWITHSUNDEEP
sqlsql-server
Cookie
Cookie

Reputation: 121

How to encode a bit literal in a sql query

I have a view which unions multiple tables. One table has a bit column called VisibleToCustomer. One of the other tables does not have this, and I want to hard code it in the view. I can do this with strings:-

SELECT  'Fred' "VisibleToCustomer", ....

or even ints

SELECT  1 "VisibleToCustomer", ....

but how do I do it with a bit? I.e. I want to do something like

SELECT  true "VisibleToCustomer", ....

but obviously the above doesn't work!

Upvotes: 3

Views: 1858

Answers (2)

Martin Smith
Martin Smith

Reputation: 453067

You can use

 SELECT  'true' AS "VisibleToCustomer"

bit has higher data type precedence than varchar so this will cast correctly.

SELECT CAST(1 AS BIT) AS "VisibleToCustomer"
UNION ALL
SELECT 'false'

Returns

+-------------------+
| VisibleToCustomer |
+-------------------+
|                 1 |
|                 0 |
+-------------------+

Upvotes: 8

Shannon Severance
Shannon Severance

Reputation: 18410

The three possible values for bit literals: select cast(1 as bit), cast(0 as bit), cast(null as bit)

Note: Any non zero values will become 1 when cast to bit, CAST(-1.2e-33 AS BIT). I would recommend against writing a bit literal that way.

Upvotes: 3

Related Questions