Name
SUBSTRING
The SUBSTRING function allows one character string to be returned from another.
ANSI SQL Standard Syntax
SUBSTRING(extraction_string
FROMstarting_position
[FORlength
] [COLLATEcollation_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
[FROMstarting_position
] [FORlength
])
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 ...
Get SQL in a Nutshell, 3rd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.