• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1432
  • Last Modified:

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

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.
  • 2
2 Solutions
select convert(char(1),f1) + convert(char(1),f2) + convert(char(1),f3) + convert(char(1),f4) as state
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.
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


select * from t1

True      False      True      True

... it will give:


Come to think about it,  you do not need  to cast.  Just do:

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

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.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now