SUBSTR Function - Oracle to MySQL 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, whereas in MySQL, if you specify 0 as the start position, an empty '' string is returned.

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 */

MySQL:

  -- 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 MySQL, 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 */

MySQL:

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

In Oracle and MySQL, 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 */

MySQL:

  -- 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, whereas in MySQL, if you specify 0 as the start position, an empty '' string is returned.

Oracle:

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

MySQL:

  -- When start position is 0, empty '' string is returned
  SELECT SUBSTR('New York', 0, 3);
  /* '' */

For more information, see Oracle to MySQL Migration.