SUBSTRING( )

This MySQL function returns the characters of a given string, starting from the position given.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

SUBSTRING(string, position[, length])
SUBSTRING(string FROM position[ FOR length])

Explanation

This function returns the characters of a given string, starting from the position given. The first character is numbered 1. You can restrict the length of the string retrieved by specifying a limit. The function is similar to MID().

Examples

SELECT CONCAT_WS('-',
   SUBSTRING(soc_sec, 1, 3),
   SUBSTRING(soc_sec FROM 4 FOR 2),
   SUBSTRING(soc_sec FROM 6)
)
AS 'Social Security Nbr.'
FROM students LIMIT 1;

+----------------------+
| Social Security Nbr. |
+----------------------+
| 433-12-3456          |
+----------------------+

This example shows the two syntaxes of SUBSTRING() for reformatting a Social Security number (the U.S. federal tax identification number) stored without dashes. It uses CONCAT_WS() to put the three pieces of data together, separated by the hyphen given.