Convert binary string to binary

Question

Convert binary string to binary

j2cagle

Old Hand

Points: 303
More actions
May 9, 2013 at 9:15 am

#273722

I have a string: '0x0000000000018662' that I want to "use" as a binary: 0x0000000000018662. CONVERT/CAST does not work because, I think, it's converting byte by byte the binary/ascii representation of each character. This seems pretty simple, but I just can't figure out how to get this to work!? Any help would be greatly appreciated!!

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

j2cagle (5/9/2013)
I have a string: '0x0000000000018662' that I want to "use" as a binary: 0x0000000000018662. CONVERT/CAST does not work because, I think, it's converting byte by byte the binary/ascii representation of each character. This seems pretty simple, but I just can't figure out how to get this to work!? Any help would be greatly appreciated!!

DECLARE @VARBINARY VARBINARY (16)

SET @VARBINARY = CONVERT(VARBINARY (16),'0x0000000000018662',1)

SELECT @VARBINARY

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

j2cagle Old Hand Points: 303 More actions · Answer 2

That's what I've been trying, but that results in:

0x30783030303030303030303030313836

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

j2cagle (5/9/2013)
That's what I've been trying, but that results in:
0x30783030303030303030303030313836

Interesting - I get the same as you if I execute the batch against SQL Server 2000. Against 2k8 it appears to work. Sorry - from here I'd be guessing.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

j2cagle Old Hand Points: 303 More actions · Answer 4

I'm using 2008R2. Is there some setting I'm missing??

Lynn Pettis SSC Guru Points: 442467 More actions Follow · Answer 5

When I run the following in SQL Server 2008 R2:

DECLARE @VARBINARY VARBINARY (16)

SET @VARBINARY = CONVERT(VARBINARY (16),'0x0000000000018662',1)

SELECT @VARBINARY;

go

DECLARE @VARBINARY VARBINARY (16)

SET @VARBINARY = CONVERT(VARBINARY (16),'0x0000000000018662',0)

SELECT @VARBINARY;

go

I get;

0x0000000000018662

0x30783030303030303030303030313836

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Lynn Pettis (5/9/2013)
When I run the following in SQL Server 2008 R2:
DECLARE @VARBINARY VARBINARY (16)
SET @VARBINARY = CONVERT(VARBINARY (16),'0x0000000000018662',1)
SELECT @VARBINARY;
go
DECLARE @VARBINARY VARBINARY (16)
SET @VARBINARY = CONVERT(VARBINARY (16),'0x0000000000018662',0)
SELECT @VARBINARY;
go
I get;
0x0000000000018662
0x30783030303030303030303030313836

Same here.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

j2cagle Old Hand Points: 303 More actions · Answer 7

I get:

0x30783030303030303030303030313836

It's almost like the "1" and "0" style options are not recognized?? Any ideas?

Lynn Pettis SSC Guru Points: 442467 More actions Follow · Answer 8

j2cagle (5/9/2013)
I get:
0x30783030303030303030303030313836
0x30783030303030303030303030313836
It's almost like the "1" and "0" style options are not recognized?? Any ideas?

Post your code, even if it is identical.

Lynn Pettis SSC Guru Points: 442467 More actions Follow · Answer 9

You say you are using SQL Server 2008 R2. What version of SQL Server are you connected to when you run these?

Run the following:

DECLARE @VARBINARY VARBINARY (16)

SET @VARBINARY = CONVERT(VARBINARY (16),'0x0000000000018662',1)

SELECT @VARBINARY;

go

DECLARE @VARBINARY VARBINARY (16)

SET @VARBINARY = CONVERT(VARBINARY (16),'0x0000000000018662',0)

SELECT @VARBINARY;

go

select @@version;

go

j2cagle Old Hand Points: 303 More actions · Answer 10

I just cut-n-pasted from above:

DECLARE @VARBINARY VARBINARY (16)

SET @VARBINARY = CONVERT(VARBINARY (16),'0x0000000000018662',1)

SELECT @VARBINARY;

go

DECLARE @VARBINARY VARBINARY (16)

SET @VARBINARY = CONVERT(VARBINARY (16),'0x0000000000018662',0)

SELECT @VARBINARY;

go

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

j2cagle (5/9/2013)
I just cut-n-pasted from above:
DECLARE @VARBINARY VARBINARY (16)
SET @VARBINARY = CONVERT(VARBINARY (16),'0x0000000000018662',1)
SELECT @VARBINARY;
go
DECLARE @VARBINARY VARBINARY (16)
SET @VARBINARY = CONVERT(VARBINARY (16),'0x0000000000018662',0)
SELECT @VARBINARY;
go

You missed the @@version...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

j2cagle Old Hand Points: 303 More actions · Answer 12

Ah ha...that would be the issue...

Version:

I know this is a 2008 forum, but is there any ideas for 2005?

Lynn Pettis SSC Guru Points: 442467 More actions Follow · Answer 13

There is no binary conversion style in SQL Server 2005. This was added in SQL Server 2008.

j2cagle Old Hand Points: 303 More actions · Answer 14

Is there any "homemade" type solution that can be done in 2005?

Convert binary string to binary

Cookies on SQLServerCentral