Solved

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

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

760 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

18 Experts available now in Live!

Get 1:1 Help Now