How to encode a bit literal in a sql query

Question

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!

Martin Smith · Accepted Answer · 2015-12-09 18:46:41Z

9

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 |
+-------------------+

answered Dec 9, 2015 at 18:46

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cookie Over a year ago

Huge thanks for such a quick and comprehensive reply, works a treat!

Shannon Severance · Accepted Answer · 2015-12-09 18:41:01Z

4

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.

edited Dec 9, 2015 at 18:41

answered Dec 9, 2015 at 18:32

Shannon Severance

18.5k4 gold badges49 silver badges67 bronze badges

4 Comments

Martin Smith Over a year ago

And CAST('true' AS BIT), CAST('false' AS BIT)

Shannon Severance Over a year ago

@MartinSmith, I did not know that. Thank you.

Cookie Over a year ago

thanks for a quick reply, used Martins but yours was good also!

Triynko Over a year ago

Also numeric string literals, as well as + and -, which are both zero: CAST('1' AS BIT), CAST('0000' AS BIT), CAST('-' AS BIT), CAST('+' AS BIT).

Collectives™ on Stack Overflow

How to encode a bit literal in a sql query

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related