SQL - Substring Function


Next Chapter »

SQL Tutorial - Substring Function


The Substring function returns a portion of the specified string value based on starting position and number of characters to be returned.

SQL Substring() SYNTAX


Select Substring(String Value, Starting Position, Number of Characters)

Note:The 'string value' can be a field name or expression that will be used as your input.The 'Starting position' indicates from where the portion of string will be extracted from. The 'Number Of characters' is the number of characters that should be extracted from the 'Starting Position'.

Substring Function Example


The Employees table has the following records:

EmployeeID EmployeeName DateOfBirth DesignationID DeptID City
1 Richard Hughes 4/23/1945 12:00:00 AM 1 1 New Orleans
2 Taryn Sinclair 3/22/1980 12:00:00 AM 2 2 San Francisco
3 Ted Horowitz 3/31/1960 12:00:00 AM 3 4 New York
4 Jonathan Douglas 7/7/1971 12:00:00 AM 3 4 Salt Lake City
5 Miranda Leigh 7/19/1983 12:00:00 AM 7 3 New York
6 Jana Rae 9/23/1976 12:00:00 AM 8 2 Houston
7 Lita Rosanna 9/14/1982 12:00:00 AM 4 6 Long Island
8 Colin Flooks 12/29/1988 12:00:00 AM 4 2 Salt Lake City
9 Anthony Frank 2/22/1988 12:00:00 AM 9 3 San Francisco
10 Stephanie Lynn 7/30/1979 12:00:00 AM 8 2 Salt Lake City
11 Jack Martin 8/25/1985 12:00:00 AM NULL NULL Austin

To extract a portion of the employee names from the 'Employees' table, use the 'Substring' function on the 'EmployeeName' column as shown below.

"Select EmployeeID,EmployeeName, Substring(EmployeeName,3,4) as SubEmployeeName
from
Employees

EmployeeID EmployeeName SubEmployeeName
1 Richard Hughes char
2 Taryn Sinclair ryn
3 Ted Horowitz d Ho
4 Jonathan Douglas nath
5 Miranda Leigh rand
6 Jana Rae na R
7 Lita Rosanna ta R
8 Colin Flooks lin
9 Anthony Frank thon
10 Stephanie Lynn epha
11 Jack Martin ck M

Note: In the above example, the 'Substring' function takes all the values from the 'EmployeeName' column and then earmarks the 3rd character as the starting position. It next extracts four characters from that point including space and displays this string in the 'SubEmployeeName' column.


Next Chapter »