SQL in a Nutshell, 2nd Edition

Name

SUBSTRING

Synopsis

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

SQL2003 Syntax

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

If any of the inputs are NULL, the SUBSTRING function returns a NULL. The extraction_string is the source where 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 extract. 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 is omitted, then the substring starting at starting_position and continuing to the end of the extraction_string is returned.

DB2

SUBSTR(extraction_string, starting_position [, length])

DB2’s implementation, SUBSTR, largely functions the same way as SQL2003’s SUBSTRING. It does not support the COLLATE clause. If length is omitted, the remainder of the string (starting at starting_position) is returned.

MySQL

SUBSTRING(extraction_string FROM starting_position)

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.

Oracle

SUBSTR(extraction_string, starting_position [, length])

Oracle’s implementation, SUBSTR, largely functions the same way as SQL2003’s SUBSTRING. It does ...