CHAR(n) Data Type - Oracle to MySQL Migration

Oracle and MySQL have CHAR(n) data type that stores a fixed-length character string right-padded with blanks to n length.

When you select data from a CHAR column in Oracle, trailing spaces are always returned:

Oracle:

  -- Sample table 
  CREATE TABLE airport_codes (code CHAR(5));
  INSERT INTO airport_codes VALUES ('SFO');
 
  -- Trailing spaces are not trimmed
  SELECT '(' || code || ')' FROM airport_codes;
  # (SFO  )

At the same time, MySQL removes the trailing spaces from CHAR columns on retrieval, by default:

MySQL:

  -- Trailing spaces are trimmed
  SELECT CONCAT('(', code, ')') FROM airport_codes;
  # (SFO)

Note that MySQL offers PAD_CHAR_TO_FULL_LENGTH option (it is not set by default) that specifies to retain the trailing spaces for CHAR columns on retrieval:

MySQL:

  SET @old_sql_mode = @@sql_mode;
 
  -- Set option to retain  trailing spaces for CHAR columns 
  SET sql_mode = PAD_CHAR_TO_FULL_LENGTH;
 
  // Trailing spaces are not trimmed now
  SELECT CONCAT('(', code, ')') FROM airport_codes;
  # (SFO  )
 
  SET sql_mode = @old_sql_mode;

• Oracle to MySQL Migration Tools and Reference

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko - April 2013.