How to query or insert a value into a binary(1) field in SQL Server 2012?

Question

I've tried googling for a while and couldn't find anything, but apologies if this is really obvious.

I have a Boolean array that is always length 8 and I want to insert the corresponding binary value into a binary(1) field. I'm using SQL Server 2012.

Currently I convert it into a string, e.g "10100101", and create a query string that looks something like "INSERT INTO Table1 VALUES('10100101');" Also have been trying to get a SELECT statement equivalent "SELECT * FROM Table1 WHERE bin_val=10100101;"

When I execute the statement I always get a syntax error. I've tried multiple variations (e.g with/without quote marks etc and putting the number in different format e.g "0xA5") with no luck.

Any suggestions?

p.s Writing code in java

Lamak · Accepted Answer · 2013-02-14 18:57:28Z

2

First, you will need to convert your binary based number to a decimal based one. Then, you can simply convert that value to the datatype BINARY. So, assuming that your string is always 8 bits long, you can try this:

DECLARE @BinaryNumber CHAR(8)
SET @BinaryNumber = '10100101'
INSERT INTO Table1
SELECT  CAST(SUBSTRING(@BinaryNumber,8,1)+
             SUBSTRING(@BinaryNumber,7,1)*2+
             SUBSTRING(@BinaryNumber,6,1)*4+
             SUBSTRING(@BinaryNumber,5,1)*8+
             SUBSTRING(@BinaryNumber,4,1)*16+
             SUBSTRING(@BinaryNumber,3,1)*32+
             SUBSTRING(@BinaryNumber,2,1)*64+
             SUBSTRING(@BinaryNumber,1,1)*128 AS BINARY(1))

answered Feb 14, 2013 at 18:57

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1883352 Over a year ago

thanks! Got it to work for INSERT INTO statement. However can't get it to for the SELECT statement... my statement is this: "SELECT * FROM CASES WHERE bin_val=CAST(3 AS BINARY(1))" It says: Incorrect syntax near '='

Rohit · Accepted Answer · 2013-02-14 18:39:32Z

0

I haven't worked on SQL Server but I think you need to something like

INSERT INTO Table1 VALUES( CAST(10100101  AS BINARY(1)) );

More info here
http://msdn.microsoft.com/en-us/library/ms188362.aspx

answered Feb 14, 2013 at 18:39

Rohit

5836 silver badges9 bronze badges

1 Comment

Lamak Over a year ago

op needs to first convert 10100101 to a decimal based value

Collectives™ on Stack Overflow

How to query or insert a value into a binary(1) field in SQL Server 2012?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related