0

I have been banging my head against a wall on this. I am trying to get data from a field in a SQL Server database that has a type of varbinary(28).

I just can't work out how to convert the data from hex back to chars.

I know that a column = '1081X' and the hex stored in the database is 0x7E959C9ABB818C.

I have tried: 

SELECT
    0x7E959C9ABB818C, '1081X',
    CONVERT(VARBINARY(28), '1081X'),
    CONVERT(VARBINARY(28), N'1081X')

-- 0x7E959C9ABB818C 1081X   0x3130383158    0x31003000380031005800

Any ideas on how to retrieve '1081X' from the code 0x7E959C9ABB818C ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
pgee70
  • 3,707
  • 4
  • 35
  • 41
  • Just to make sure I give you the right solution, the value in the binary field is ASCII text? – tj-cappelletti Jan 27 '18 at 23:21
  • Possible duplicate of [varbinary to string on SQL Server](https://stackoverflow.com/questions/3289988/varbinary-to-string-on-sql-server) – Vahid Farahmandian Jan 27 '18 at 23:22
  • @virusstorm i think so. i have tried UTF and ASCII but cant work it out. – pgee70 Jan 27 '18 at 23:58
  • Start over. Exactly **how** do you know that this column contains the text value you provided? – SMor Jan 28 '18 at 00:32
  • Yes, i am trying to integrate with a vendor database. The row has information on a drug including its name in text. For that drug i can look up the PBS code for that drug, which is 1081X – pgee70 Jan 28 '18 at 02:54

1 Answers1

1

I get different results.

Are you sure the value 0x7E959C9ABB818C should translate to: '1081X'

--what OP appears to be asking:
select cast(0x7E959C9ABB818C as varchar(max))  --result: ~•œš»Œ

--what does cast of OP question provide?
select cast('1081X' as varbinary(max))   --result: 0x3130383158

--test cast back
select cast(0x3130383158 as varchar(max))  --result: 1081X
Brien Foss
  • 3,336
  • 3
  • 21
  • 31
  • Yes, i am trying to integrate with a vendor database. The row has information on a drug including its name in text. For that drug i can look up the PBS code for that drug, which is 1081X – pgee70 Jan 28 '18 at 02:54
  • @pgee70 I got the same result as Brien. I don't think the binary value you are working with is what you think it is. – tj-cappelletti Jan 29 '18 at 13:58
  • @pgee70 Hello again, I am coming back to this.. curious did you ever figure out what the problem/solution was? If so, maybe post it as an Answer and accept your own answer? – Brien Foss Feb 25 '18 at 15:54