varchar to binary conversion

Question

varchar to binary conversion

arup chakraborty

Hall of Fame

Points: 3640
More actions
March 19, 2012 at 2:25 am

#98625

Hi,
I am not able to convert 0x2139aec19f212447bb23ba307d611a1e to BINARY(16) form. If I am using cast/convert and put the value directly, then it is giving proper result i.e.0x2139AEC19F212447BB23BA307D611A1E which is a binary. But if I am using the same by some column name, it is giving 0x30007800320031003300390061006500
Please help as I am stucked with this for last 2 days.
Regards,
Arup

Viewing 12 posts - 1 through 11 (of 11 total)

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

Ant-Green SSC Guru Points: 113445 More actions · Answer 1

please can you post the DDL for the table and some sample data with the results you expect to see and what you do actually see so we can take a better look for you

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 2

Hi Anthony,

Thanks for quick reply. Actually I am parsing this string from a ntext column and storing this inside a table variable. If I am putting my table variable structure as declare

@temp TABLE (Object_ID nvarchar(255), License nvarchar(100)), then it is working fine but I need to make Object_ID column as varchar to JOIN with other tables.

Once I am putting this as declare

@temp TABLE (Object_ID BINARY(16)), License nvarchar(100)) then, it is giving rubbish results.

Ant-Green SSC Guru Points: 113445 More actions · Answer 3

well looking at the cast/convert MSDN page

http://msdn.microsoft.com/en-us/library/ms187928.aspx

it details that ntext to binary convertions are not allowed, you will need to convert to varchar or nvarchar

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 4

Hi Anthony,

This is not the problem with ntext conversion. I ahve extracted uid as nvarchar from the ntext. Now need to convert that uid to binary which is throwing error.

declare @Q1 nvarchar(255), @Q2 BINARY(16)

select @Q1=0x2139aec19f212447bb23ba307d611a1e --as a1, cast(0x2139aec19f212447bb23ba307d611a1e as binary(16)) as a2

select @Q2=cast(0x2139aec19f212447bb23ba307d611a1e as binary(16))

select @Q2

This is working fine. If if u use only variable, then it is throwing error.

Ant-Green SSC Guru Points: 113445 More actions · Answer 5

so back to my original post, please provide the create table definition for the table containing the ntext column, please provide sample data and also post the full query you have so far

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 6

Hi,

Sorry as I cant post code or table def. for security reason.

Could you just answer one thing:

I have this type of column( ex. 0x2139aec19f212447bb23ba307d611a1e) already in binary format but the data type is nvarchar.

Can I convert the same thing so that it will show as 0x2139aec19f212447bb23ba307d611a1e but data type will be BINARY?

Regards,

Arup

Ant-Green SSC Guru Points: 113445 More actions · Answer 7

your problem is your storing binary in a textual data type not as a binary data type which is why you are getting the different errors

select CONVERT(binary(16),'0x2139aec19f212447bb23ba307d611a1e') will give you 0x30783231333961656331396632313234

select CONVERT(varchar,0x2139aec19f212447bb23ba307d611a1e) will give you !9®ÁŸ!$G»#º0}a

select CONVERT(binary(16),'!9®ÁŸ!$G»#º0}a') will give you 0x2139aec19f212447bb23ba307d611a1e

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 8

arup chakraborty

Hall of Fame

Points: 3640

March 19, 2012 at 3:58 am

#1461045

Thanks Anthony, I will try this and let you know

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 9

anthony.green (3/19/2012)
your problem is your storing binary in a textual data type not as a binary data type which is why you are getting the different errors
select CONVERT(binary(16),'0x2139aec19f212447bb23ba307d611a1e') will give you 0x30783231333961656331396632313234
select CONVERT(varchar,0x2139aec19f212447bb23ba307d611a1e) will give you !9®ÁŸ!$G»#º0}a
select CONVERT(binary(16),'!9®ÁŸ!$G»#º0}a') will give you 0x2139aec19f212447bb23ba307d611a1e

Hi Anthony,

Can I convert 0x30783231333961656331396632313234 value to 0x2139aec19f212447bb23ba307d611a1e as binary? As other 2 conditions are not suitable in my case as I am storing bnary data in textual form and want to convert the same to BINARY data type.

Ant-Green SSC Guru Points: 113445 More actions · Answer 10

in short no, the issue is in your design, you will never get the right value as its a string as a base data type

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 11

arup chakraborty

Hall of Fame

Points: 3640

March 19, 2012 at 4:50 am

#1461055

Thanks Anthony