CODEWITHSUNDEEP
javasqlsql-serverbinarysql-server-2012
user1883352
user1883352

Reputation: 53

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

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

Upvotes: 1

Views: 7192

Answers (2)

Lamak
Lamak

Reputation: 70648

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))

Upvotes: 2

Rohit
Rohit

Reputation: 583

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

Upvotes: 0

Related Questions