LENGTH Function - Oracle to MariaDB Migration

In Oracle the LENGTH function returns the string length in characters.

MariaDB also provides the LENGTH function, but it returns the string length in bytes in native mode, and length in characters in Oracle compatibility mode.

In native mode, you have to convert Oracle LENGTH to MariaDB CHAR_LENGTH function that returns the string length in characters.

Oracle:

  -- Regular ANSI string
  SELECT LENGTH('abc') FROM dual;
  # 3
 
  -- Length of € sign in Unicode that takes 2 bytes of storage
  SELECT LENGTH(u'\20AC') FROM dual;
  # 1

MariaDB:

  -- Regular ANSI string
  SELECT CHAR_LENGTH('abc') FROM dual;
  # 3
 
  -- Length of € sign in Unicode that takes 2 bytes of storage
  SELECT CHAR_LENGTH(_utf16 x'20AC');
  # 1

Compare if you use MariaDB LENGTH function for Unicode string:

MariaDB:

  -- Length of € sign in Unicode in bytes
  SELECT LENGTH(_utf16 x'20AC');
  # 2

But if you use LENGTH function in Oracle compatibility mode:

MariaDB - Oracle Compatibility Mode:

  SET sql_mode=oracle;
 
  -- Length of € sign in Unicode in characters now (Oracle mode)
  SELECT LENGTH(_utf16 x'20AC');
  # 1

For more information, see Oracle to MariaDB Migration.