SQL Substring Function
SQL > SQL String Functions >
The Substring function in SQL is used to return a portion of string. This function is called differently in different databases:
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.
Below are some examples. Assume we have the following table:
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 this example, 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.'