4

I need the text (representation) of a id field in SQL Server 2005. Is there a way, we can generate the textual representation of the id field?

For instance, if the id field reads as 0x00000000000002F0, I need the text value of 0x00000000000002F0 so that I can run SUBSTR operations on the same.

Constraints

  1. I am not allowed to create a stored procedure in the Database (as creation of SP is not allowed)

Thanks!

Kanini
  • 1,947
  • 6
  • 33
  • 58
  • What is the datatype of the ID field? – Ed Harper Sep 23 '10 at 09:23
  • How are you going to encode the binary to text? Is it ASCII, Unicode, UTF8 etc? What is it you're ultimately trying to do? – PaulG Sep 23 '10 at 09:51
  • PaulG: Encode the text to Unicode. Basically, we are trying to store this value in another field so that we can eventually do a text matching. – Kanini Sep 23 '10 at 10:14

2 Answers2

4

You can convert unicode strings to binary using 

SELECT CONVERT(VARBINARY(40),N'Hello World')
(returns 0x480065006C006C006F00200057006F0072006C006400)

Convert from binary back to unicode using

SELECT CONVERT(NVARCHAR(20), 0x480065006C006C006F00200057006F0072006C006400)
(returns 'Hello World')
PaulG
  • 13,871
  • 9
  • 56
  • 78
3

Whilst it's not immediately obvious to me why you would want to do this for comparison purposes (as opposed to matching binary values), the undocumented function sys.fn_varbintohexstr should do the trick

declare  @vb binary(8)
        ,@vc varchar(20)

set @vb = 0x00000000000002F0
set @vc = sys.fn_varbintohexstr(@vb)

--prove that this works by concatenating a string to the varchar value
select @vb, '#' + @vc
Ed Harper
  • 21,127
  • 4
  • 54
  • 80