CHAR Function - SQL Server to MySQL Migration

In SQL Server, the CHAR function converts an integer ASCII code (0-255) to the corresponding character value. MySQL also provides the CHAR function but it returns a binary string by default, so you should add USING ASCII clause.

SQL Server:

  -- CHAR(32) returns a blank character
  SELECT CHAR(32);
  /* Result: ' ' */
 
  -- NULL is returned for values exceeding 255
  SELECT CHAR(270);
  /* NULL */

MySQL:

  -- CHAR(32) returns 0x20 binary string
  SELECT CHAR(32);
  /* Result: 0x20 */
 
  SELECT CHAR(32 USING ASCII);
  /* Result: ' ' */

Why USING ASCII is required? 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', CHAR(32 USING ASCII), 'B');
  /* Result: A B */

You can see that the result of concatenation is a binary string if you do not use USING ASCII clause in MySQL.

Notes on MySQL CLI Client

By default, if you run MySQL CLI client in interactive mode, it sets --binary-as-hex option, so you see hex values returned by the CHAR function.

If you run MySQL CLI client with --skip-binary-as-hex you will get ASCII characters even without USING ASCII:

MySQL:

  -- Running MySQL CLI client with --skip-binary-as-hex
  SELECT CHAR(65);
  /* Result: A */

But note it is just that the MySQL client converts hex values to ASCII for representation in the terminal. CHAR() still returns binary string, so adding USING ASCII still makes sense.

To see how the current session of the MySQL client represents binary data you can run status or \s command:

  mysql> status
  ...
  Binary data as:         Hexadecimal

For more information, see SQL Server to MySQL Migration.