SQL Substring Function
SQL > SQL String Functions >
The Substring function in SQL is used to return a portion of the string. Each database provides its own way(s) of doing this:
The syntax for SUBSTRING is as follows (we will use SUBSTR( ) here):
SUBSTR (str, position, [length])
where position and length are both integers. This syntax means the following: Start with the position-th character in string str, select the next length characters.
In MySQL and Oracle, length is an optional argument. When length is not specified, the entire string starting from the position-th character is returned. In SQL Server, length is required.
We use the following table for our examples.
SELECT SUBSTR (Store_Name, 3)
WHERE Store_Name = 'Los Angeles';
SELECT SUBSTR (Store_Name, 2, 4)
WHERE Store_Name = 'San Diego';
ORDER BY SUBSTR (Store_Name, 2, 4);
In Example 3, the ORDER BY criteria is based on the 2nd to 5th characters in the string. Below are the 2nd to 5th characters for each Store_Name:
Based on the above, we can see that 'San Diego' would be first, followed by 'New York,' 'Chicago,' and finally 'Los Angeles.'