MySQL in a Nutshell, 2nd Edition

Name

SUBSTRING()

Synopsis

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

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(). Here is an example:

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.