How to do a Select concat on bit fields in SQL Server?

I have 4 bit (boolean) fields that together comprise the status of the record.
If taken as a group, these bits might be 0001, 0010, 0011, etc for the 16 possible
states.

What I can't figure out is how to select them as a single value. Everyone I ask
suggests using  "select concat (f1, f2, f3, f4) as state" but concat is a MySQL dialect
only, I think.

Can someone tell me how to have these 4 binary values returned as one.
451lsAsked:
Who is Participating?
 
vc01778Connect With a Mentor Commented:
Come to think about it,  you do not need  to cast.  Just do:

select 8*f1+4*f2+2*f3+f4 from t1

VC
0
 
arbertConnect With a Mentor Commented:
select convert(char(1),f1) + convert(char(1),f2) + convert(char(1),f3) + convert(char(1),f4) as state
0
 
451lsAuthor Commented:
OK, that's pretty close. That gives me a character map that looks binary but isn't converted to a numeric value. Could you tell me how to get it as decimal (or hex) such that a 1101 is returned as '13'. If I can do that, it's perfect.

I wondered if you thought that doing a calculated formula in the database would be better, more efficient. For example, defining a column 'state' that is calculated as the binary concatenation of the f1,f2,f3,f4. I'm not sure how to do that, but it seems like it might be faster.
0
 
vc01778Commented:
You need to shift the bits into a proper position:

select 8*cast(f1 as tinyint) +
       4*cast(f2 as tinyint) +
       2*cast(f3 as tinyint) +
       1*cast(f4 as tinyint)
from t1

for

select * from t1

True      False      True      True

... it will give:

13


VC
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.