CHAR Function - SQL Server to MariaDB Migration

In SQL Server, the CHAR function converts an integer ASCII code (0-255) to the corresponding character value. MariaDB 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 */

MariaDB:

  -- CHAR(32) returns 0x20 binary string but it's shown as ASCII by MariaDB client (see notes below)
  SELECT CHAR(32);
  /* Result: ' ' */
 
  SELECT CHAR(32 USING ASCII);
  /* Result: ' ' */

By default, if you run MariaDB CLI client in interactive mode, it translates hex data to ASCII in the terminal that's why you can see ASCII characters when using the CHAR function.

But note it is just a representation of binary data in the terminal. CHAR() still returns binary string, so adding USING ASCII still makes sense.

If you run MariaDB CLI client with --binary-as-hex option you will get hex characters:

MariaDB:

  -- Running MariaDB CLI client with --binary-as-hex
  SELECT CHAR(65);
  /* Result: 0x41 */

For more information, see SQL Server to MariaDB Migration.