Solved

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

Posted on 2004-04-04
4
1,362 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now