Solved

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

Posted on 2004-04-04
4
1,373 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:451ls
  • 2
4 Comments
 
LVL 34

Assisted Solution

by:arbert
arbert earned 100 total points
ID: 10753860
select convert(char(1),f1) + convert(char(1),f2) + convert(char(1),f3) + convert(char(1),f4) as state
0
 

Author Comment

by:451ls
ID: 10753936
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
 
LVL 11

Expert Comment

by:vc01778
ID: 10753977
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
 
LVL 11

Accepted Solution

by:
vc01778 earned 250 total points
ID: 10753991
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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question