SUBSTRING
Complete SUBSTRING() reference: syntax forms (pos,len, FROM/FOR), negative position from end, NULL handling, and Oracle sql_mode position 0 behavior.
Syntax
SUBSTRING(str,pos),
SUBSTRING(str FROM pos),
SUBSTRING(str,pos,len),
SUBSTRING(str FROM pos FOR len)
SUBSTR(str,pos),
SUBSTR(str FROM pos),
SUBSTR(str,pos,len),
SUBSTR(str FROM pos FOR len)Description
The forms without a len argument return a substring from string str starting at position pos.
The forms with a len argument return a substring len characters long from string str, starting at position pos.
The forms that use FROM are standard SQL syntax.
It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function.
By default, the position of the first character in the string from which the substring is to be extracted is 1. If the value of pos is 0, the result is empty string. For Oracle compatibility, when sql_mode is set to 'oracle', position 0 is treated as position 1.
If any argument is NULL, returns NULL.
The optimizer can make use of an index for conditions like SUBSTR(indexed_column, 1, n) = const_string.
The optimizer cannot make use of an index if an indexed column is an argument of SUBSTR().
Examples
Oracle mode:
See Also
INSTR() - Returns the position of a string within a string
LOCATE() - Returns the position of a string within a string
SUBSTRING_INDEX() - Returns a string based on substring
This page is licensed: GPLv2, originally from fill_help_tables.sql
Last updated
Was this helpful?

