SUBSTR Function - Oracle to MariaDB Migration

In Oracle and MariaDB, the SUBSTR function returns a substring from a string, starting at the specified position (which can be negative) and for the specified length, or until the end of the string if no length is provided.

In Oracle, a start position of 0 is treated as 1. In MariaDB (since 10.3.3), this behavior only occurs when Oracle Compatibility mode is enabled.

Oracle:

  -- Get first 3 characters
  SELECT SUBSTR('New York', 1, 3) FROM dual;
  /* New */
 
  -- Get last 4 characters (negative start position)
  SELECT SUBSTR('New York', -4) FROM dual;
  /* York */

MariaDB:

  -- Get first 3 characters
  SELECT SUBSTR('New York', 1, 3);
  /* New */
 
  -- Get last 4 characters (negative start position)
  SELECT SUBSTR('New York', -4);
  /* York */

In Oracle and MariaDB, if the length is not specified, the substring extends to the end of the string.

Oracle:

  -- Get substring from position 5 until the end of string
  SELECT SUBSTR('New York', 5) FROM dual;
  /* York */

MariaDB:

  -- Get substring from position 5 until the end of string
  SELECT SUBSTR('New York', 5);
  /* York */

In Oracle and MariaDB, if the start position is negative the SUBSTR function starts counting from the end of the string.

Oracle:

  -- Get 3 characters from position 4 counting from the end of string
  SELECT SUBSTR('New York', -4, 3) FROM dual;
  /* Yor */

MariaDB:

  -- Get 3 characters from position 4 counting from the end of string
  SELECT SUBSTR('New York', -4, 3);
  /* Yor */

In Oracle, a start position of 0 is treated as 1. In MariaDB, this behavior only occurs when Oracle Compatibility mode is enabled.

Oracle:

  -- A start position of 0 is treated as 1  
  SELECT SUBSTR('New York', 0, 3) FROM dual;
  /* New */

MariaDB:

  -- When start position is 0, empty '' string is returned in non-Oracle mode  
  SELECT SUBSTR('New York', 0, 3);
  /* '' */
 
  -- Enable Oracle compatibility
  SET sql_mode = oracle;
 
    -- A start position of 0 is treated as 1 in Oracle compatibility mode 
  SELECT SUBSTR('New York', 0, 3);
  /* New */

For more information, see Oracle to MariaDB Migration.