Convert BINARY to VARCHAR

Question

Convert BINARY to VARCHAR

David Kranes

SSCrazy

Points: 2523
More actions
March 14, 2011 at 12:30 pm

#236169

Hello, i am trying to convert a binary value to a varchar and I am getting incorrect results. Here is what I am doing:
select distinct
CONVERT(VARCHAR(100), h.HRCHY_ID, 1) AS fConvert,
CAST(h.HRCHY_ID AS VARCHAR(100)) AS fCast,
h.HRCHY_DESC
from
ORG_CHN_HRCHY h JOIN csaRoleLocationAttributes r on
where
h.ACTV = 1
And R.Retailer_Id = 7
and r.role_id = 'FnF-POWER'
and CONVERT(VARCHAR(100), h.HRCHY_ID, 1) = r.LocationHierarchyID
Notice that the CAST function seems to be incorrect. Any ideas on how to correct this? I need to be able to use CAST in order to have common code across RDBMS platforms. Thank you.
David
Best Regards,
~David

Viewing 5 posts - 1 through 4 (of 4 total)

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

sturner SSC-Insane Points: 22411 More actions · Answer 1

I have been using master.sys.fn_varbintohexsubstring() to convert varbinary to a hexadecimal string value.

Some people get bent out of shape because it is supposedly "undocumented" but it works fine and I use it in several scripts without issue.

if, on the other hand, the (var)binary data actually contains valid ascii characters you can just use CAST.

The probability of survival is inversely proportional to the angle of arrival.

David Kranes SSCrazy Points: 2523 More actions · Answer 2

Okay, my initial query was incorrect. Here is the actual query and the incorrect CAST results:

select distinct

CONVERT(VARCHAR(100), h.HRCHY_ID, 1) fConvert,

CAST(h.HRCHY_ID AS VARCHAR(100)) fCast

from

ORG_CHN_HRCHY h,

csaRoleLocationAttributes r

where

h.ACTV = 1

and R.Retailer_Id = 7

and r.role_id = 'FnF-POWER'

and CONVERT(VARCHAR(100), h.HRCHY_ID, 1) = r.LocationHierarchyID

Results:

fConvert fCast

0xFD951AA3A39B9D4EA41646E6DB06C255ý•££›N¤FæÛÂU

The fCast column should be the same as the fConvert. I was hoping to use CAST as it is available in both SQL server and Oracle.

Best Regards,

~David

Lowell SSC Guru Points: 323518 More actions · Answer 3

i believe binary must be converted to nvarchar, right? so the conversion to varchar is failing?

select distinct

CONVERT(NVARCHAR(100), h.HRCHY_ID, 1) fConvert,

CAST(h.HRCHY_ID AS NVARCHAR(100)) fCast

from

ORG_CHN_HRCHY h,

csaRoleLocationAttributes r

where

h.ACTV = 1

and R.Retailer_Id = 7

and r.role_id = 'FnF-POWER'

and CONVERT(NVARCHAR(100), h.HRCHY_ID, 1) = r.LocationHierarchyID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sturner SSC-Insane Points: 22411 More actions · Answer 4

perhaps the string is in UNICODE (or some other encoding?) If so you would need to say:

CAST(h.HRCHY_ID AS NVARCHAR(100)) fCast

The probability of survival is inversely proportional to the angle of arrival.