SQL in a Nutshell

Name

SUBSTRING

Synopsis

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

SQL99 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 where the character value is extracted from. 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 perform the extract. The optional length is an integer value that tells the function how many characters to extract, starting at the starting_position.

MySQL Syntax and Variations

SUBSTRING(extraction_string FROM starting_position)

MySQL’s implementation assumes that the characters are to be extracted from the starting position continuing to the end of the character string.

Microsoft SQL Server Syntax and Variations

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

Microsoft SQL Server largely supports the SQL99 standard, except that it does not allow the COLLATE clause. Microsoft allows this command to be applied to text, image, and binary datatypes; however, the starting_position and length represent the number of bytes rather than the number of characters to count.

Oracle Syntax and Variations

SUBSTR(extraction_string, starting_position [, length])

Oracle’s implementation, SUBSTR, largely functions the same way as SQL99. It does not support ...