5

I have some base-64 encoded strings in SQL Server database, for example:

DECLARE @x VARBINARY(64);
SET @x = 0x4b78374c6a3733514f723444444d35793665362f6c513d3d

When it's CAST or CONVERTED to a VARCHAR, I get:

+˽Ð:¾Îréî¿•

I'm looking for SQL Server to return a varchar with the hexadecimal representation of the varbinary as a varchar, e.g.:

4b78374c6a3733514f723444444d35793665362f6c513d3d

Is there a build in CAST/CONVERT/function that does this, or does it have to be added as a User Defined Function? And what would the UDF be?

Bonus points if I can select whether I want capital A-F or lower case a-f in the conversion process.

Improve this question

4 Answers 4

Reset to default
5

For SQL2005 I would use xsd:hexBinary data type:

DECLARE @x VARBINARY(64);
SET @x = 0x4b78374c6a3733514f723444444d35793665362f6c513d3d

SELECT '0x'+CONVERT(XML, '').value('xs:hexBinary(sql:variable("@x") )', 'VARCHAR(MAX)');
SELECT '0x'+LOWER(CONVERT(XML, '').value('xs:hexBinary(sql:variable("@x") )', 'VARCHAR(MAX)'));

For SQL2008+ I would use CONVERT (see section Binary styles):

SELECT CONVERT(VARCHAR(MAX), @x, 1)

References: http://blogs.msdn.com/b/sqltips/archive/2008/07/02/converting-from-hex-string-to-varbinary-and-vice-versa.aspx

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Your example uses both hexBinary and CONVERT; could you clarify which is SQL2005 and which is SQL2008+?
@Ehryk: See the last example from blogs.msdn.com/b/sqltips/archive/2008/07/02/…
Could you add that to your answer?
@Ehryk: Example added.
2 
DECLARE @x VARBINARY(64);
SET @x = 0x4b78374c6a3733514f723444444d35793665362f6c513d3d;

SELECT CONVERT(VARCHAR(64), @x),
       SUBSTRING(CONVERT(VARCHAR(64), @x, 1), 3, 64);
       ----- style number is important ---^

Results:

Kx7Lj73QOr4DDM5y6e6/lQ==    4B78374C6A3733514F723444444D35793665362F6C513D3D

If you want lower case, just wrap the whole SUBSTRING operation in LOWER().

SQLFiddle demo

Improve this answer

Comments

1

The suggested solution, SELECT CONVERT(VARCHAR(MAX), @x, 1), returns "0x4B78374C...", but the question clearly wanted the result to be "4b78374c...".

By modifying the "style" parameter to CONVERT() and adding LOWER(), we get exactly what the questioner was looking for, without using SUBSTRING() or RIGHT():

SELECT LOWER(CONVERT(VARCHAR(MAX), @x, 2))
Improve this answer

Comments

0

For SQL 2008+

SELECT CONVERT(VARCHAR(MAX), @x, 1)
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.