Convert MSSQL string to Hex and unhex value in MySQL

Question

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

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

It returns:

It's great! But I know that I want to unhex the exact string in MySQL:

So, in MySQL, the string looks a little different from the string I get from MSSQL. Perhaps I could replace the string or something

But with a more complex line, it even changes between mssql and mysql:

- The same procedures as before, just tried a different line: -

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

The beginning is the same (possibly due to SAM), but there seems to be a problem with special characters ...: - (

Answer 1

The hexadecimal encodings of SQL Server and MySQL begin to diverge at position 11. The character at this position is ä , the first non-ascii character. Therefore, there is a likely reason to believe that each database uses a different encoding.

MySQL coding is UTF-8

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

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

The reason it is disabled is your MySQL client, which points to a cutoff with ... at the end.

SQL Server encoding is Latin1_General (aka Windows-1252)

SQL Server Encoding ä - 0xE4 . It is probably encoded in SQL Server Latin1_General , which corresponds to Windows-1252 . Other öüß characters öüß converted to 0xF6FCDF under Windows-1252, confirming the assumption.

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

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

The SQL Server hexadecimal string is disconnected due to your cast(... as varbinary) . If n is not specified using the CAST function, the default length is 30 . Try explicitly specifying the size or setting it to max :

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

Answer 2

This request can solve this problem:

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