Convert Binary Id Field to Text

问题:

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!

回答1:

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')

回答2:

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