Convert a MSSQL String to Hex and unhex the value in MySQL

Question

I want to convert a string in Microsoft SQL to hexdecimal value, for example this:

declare @b varbinary(max)
set @b = (Select cast('Ali' as varbinary))

select cast(@b as varchar(max))
select @b

It returns:

This is great! But know I want to unhex the exact string in MySQL:

So in MySQL the string looks a bit different from the string I get from MSSQL. Maybe I could do a string replace or something

But with a more complex string it even varys between mssql and mysql:

-- Same procedure as before, just tried another string: --

MSSQL-String: 0x53414D31302F32303130E4F6FCDF5C2A23E92D656E64657C3C6469762073
MySQL-String:   53414D31302F32303130C3A4C3B6C3BCC39F2A23C3A92D656E

The beginning is the same (maybe because of the SAM), but it seems to be a problem with the special chars ... :-(

Answer 1

The SQL Server and MySQL hex encodings start to diverge at position 11. The character at that position is ä, the first non-ascii character . So there is probable cause to believe that each database uses a different encoding.

The MySQL encoding is UTF-8

The encoding of ä in UTF-8 is 0xC3A4, so that's what MySQL uses. This is confirmed by the utf-8 decoder:

53414D31302F32303130C3A4C3B6C3BCC39F2A23C3A92D656E
-->
SAM10/2010äöüß*#é-en

The reason it is cut off is your MySQL client, which indicates the cut off with ... at the end.

The SQL Server encoding is Latin1_General (aka Windows-1252)

The SQL Server encoding of ä is 0xE4. It's probably encoded in SQL Server's Latin1_General collation, which corresponds to Windows-1252. The other characters öüß convert to 0xF6FCDF under Windows-1252, confirming the guess.

To force SQL Server to use a different encoding, specify the collate clause:

cast('öüß' AS varchar(5)) collate French_CS_AS

The SQL Server hex string is cut off because of your cast(... as varbinary). When n is not specified with the CAST function, the default length is 30. Try to specify the size explicitly, or set it to max:

cast('abcd' as varbinary(max))
                        ^^^^^

Answer 2

This query may solve the purpose:

Declare @b varbinary(max)
Select @b=Cast(CONVERT(varbinary(4), '0x' + @HexValue, 1) As varbinary)