Converting a String to Hex in SQL

convert text string to hexadecimal representation or binary representation

When I run the following

select distinct convert (varbinary, 'Hogan') as Employee

I get the following

Employee
--------------------------------------------------------------
0x486F67616E

Maybe there is a problem in the way you are displaying the results?

How to convert a String to Hex and vice-versa?

I think this should work

SELECT     hex(CAST("Ali" AS VARBINARY)) AS Expr1

for vice versa

select CONVERT(varbinary(max), "416C69");

This will convert to varbinary, then you can convert varbinary to varchar

Converting a String to HEX in SQL

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

Convert varchar to hexadecimal in sql server

You can try to use CONVERT function:

SELECT CONVERT(VARBINARY(MAX), 'myText') 

Output:

0x6D7954657874

Convert decimal to hex string without 0x in SQL Server

Convert to hex using the system function master.dbo.fn_varbintohexstr, then remove the first two characters.

SELECT SUBSTRING(master.dbo.fn_varbintohexstr(convert(binary(2), 'B1')),3,999)

Output:

4231

Select Hex/Char conversion

The 00 point to 2-byte-enocding which is represented as NVARCHAR. Try this

SELECT CAST(0x540045005300540049004E00470031003200330034 AS NVARCHAR(MAX))

Or directly from the HEX-string as string:

SELECT CAST(CONVERT(VARBINARY(MAX),'540045005300540049004E00470031003200330034',2) AS NVARCHAR(MAX));

The result is TESTING1234

Some more background on string encoding

SQL-Server knows exactly two types of strings:

• 1-byte-encoded VARCHAR / CHAR
• 2-byte-encoded nVARCHAR / nCHAR

The 1-byte string is extended ASCII, the related collation provides a code page to map non-plain-latin characters (it is not utf-8 as people sometimes tell).

The 2-byte string is UCS-2 (almost the same as utf-16).

I've corrected the word unicode above, as it is not correct actually.

There are many encodings SQL-Server will not be able to interpret natively.

The string above looks like it is good for NVARCHAR, but this is not guaranteed in any case.

Some more background on binary encoding

SQL-Server knows BINARY and VARBINARY as a real BLOB-Type. In the result of a SELECT they are presented as HEX-string and in a script you can use a HEX-string as native input. But it is important to know, that this HEX-string is not the actual value!, just the human readable representation on a computer screen.

And there is a real string, which looks like a HEX-string (but isn't).

0x123 != '0x123'

If you have a string, which is a HEX-string, but is coming to you as "normal" string (e.g. in a text based container like a CSV file or an XML) you have to convert this.

And, not really related to this question, just to mention it: There are more string based binary representers like base64.

Some examples

--start with a normal string 
DECLARE @str VARCHAR(100)='This is a test to explain conversions from string to hex to binary and back';
--see the HEX string (real binary!)
SELECT CAST(@str AS VARBINARY(MAX)) ThisIsTheHexStringOfTheString;

--I copy the binary behind the "=" _wihtout_ quotes
DECLARE @ThisIsTheBinary VARBINARY(MAX)=0x546869732069732061207465737420746F206578706C61696E20636F6E76657273696F6E732066726F6D20737472696E6720746F2068657820746F2062696E61727920616E64206261636B;
--This can be re-casted directly
SELECT CAST(@ThisIsTheBinary AS VARCHAR(MAX)) ThisIsReconvertedBinary;

--there is an undocumented function providing a HEX-string from a binary
DECLARE @aHEXstring VARCHAR(MAX)=sys.fn_varbintohexstr(CAST(@str AS VARBINARY(MAX)));
--This string looks exactly the same as above, but it is a string
SELECT @aHEXstring AS ThisIsStringWhichLooksLikeHEX;

--You can use dynamic SQL
EXEC('SELECT CAST(' + @aHEXstring + ' AS VARCHAR(MAX)) AS CastedViaDynamicSQL');
--or CONVERT's abilities (read the documentation!)
SELECT CAST(CONVERT(VARBINARY(MAX),@aHEXstring,1) AS VARCHAR(MAX)) AS ConvertedViaCONVERT

Hex to ASCII string conversion on the fly

According to your information, that this column is from Microsoft Dynamics NAV it sounds really clear.

The MSDN documentation says this:

Specifies the record that the link is attached to.

The following syntax is used:

<table name>: <primary key1>, <primary key2>, <primary key3>

I'm pretty sure, Microsoft has a combined schema for this value, which is a bit strange but well not that hard to fiddle out I think.

If you split your values up to the FF, you can see two results:

--0xB6A1E6050089FF4252412D4D3032000000
select convert(int,0xB6A1E6050089FF) -- Object_ID for the specified table
select convert(varchar(100),0x4252412D4D3032000000) -- Primary Key 1

You can try to take this further and execute this on your machine:

select object_name(convert(int,0xB6A1E6050089FF))

This should give you a valid table name. Hopefully. ;-)

I can't check this on my side, as I havent those system installed. If so, I can add a short command to this result to automate the splitting for you.

Your other string can be splitted like this:

--0x1E150000008B000000000089FF534F3030303133353532000087102700000000
SELECT convert(int,0x1E150000008B000000000089FF) -- 35327 (object_id)
SELECT convert(varchar(100),0x534F30303031333535320000) -- SO00013552
SELECT convert(int,0x87102700000000) -- 0

I think, you need to parse it dynamically. You should check the basetable (the first part). After that, check the primary keys in their ordering. After checking this, you can dynamically parse them into their target format. The only thing, which may be a bit harder, is the part of the splitting. Normally the first part should go until the FF. After that each part can be splitted by multiple zeros.

---

Related Topics