SQL in a Nutshell, 3rd Edition

Name

SUBSTRING

The SUBSTRING function allows one character string to be returned from another.

ANSI SQL Standard Syntax

SUBSTRING(extraction_string FROM starting_position [FOR length]
[COLLATE collation_name])

If any of the inputs are NULL, the SUBSTRING function returns NULL. The extraction_string is the source from which the character value is to be extracted. It may be a literal string, a column in a table with a character datatype, or a variable with a character datatype. The starting_position is an integer value telling the function at which position to begin performing the extraction. The optional length is an integer value that tells the function how many characters to extract, starting at the starting_position. If the optional FOR keyword is omitted, the substring starting at starting_position and continuing to the end of the extraction_string is returned.

MySQL

MySQL largely supports the ANSI standard, but it does not accept the COLLATE clause. MySQL’s implementation assumes that the characters are to be extracted from the starting position and will continue to the end of the character string. The syntax is as follows:

SUBSTRING(extraction_string [FROM starting_position] [FOR length])

Oracle

Oracle’s implementation, SUBSTR, largely functions the same way as ANSI SQL’s SUBSTRING, but Oracle does not support the COLLATE clause. When the starting_position is a negative number, Oracle counts from the end of the extraction_string. If length is omitted, the remainder of the string (starting ...