CHAR() Function - SQL Server to MySQL Migration

In SQL Server you can use CHAR function to convert an integer ASCII code to the corresponding character value. MySQL also provides CHAR function but it returns a binary string, so you should apply CAST AS CHAR expression.

SQL Server:

  -- CHAR(32) returns a blank character
  SELECT CHAR(32);
  # Result: ' '

MySQL:

  -- CHAR(32) returns 0x20 binary string
  SELECT CHAR(32);
  # Result: 0x20
 
  SELECT CAST(CHAR(32) AS CHAR);
  # Result: ' '

Why CAST may be required during the migration? Consider a string concatenation involving CHAR() function:

SQL Server:

  SELECT 'A' + CHAR(32) + 'B';
  # Result: A B

MySQL:

  SELECT CONCAT('A', CHAR(32), 'B');
  # Result: 0x412042
 
  SELECT CONCAT('A', CAST(CHAR(32) AS CHAR), 'B');
  # Result: A B

You can see that the result of concatenation is a binary string if you do not use CAST AS CHAR expression in MySQL.

Database and SQL Migration Tools