The Substring function in SQL is used to return a portion of string. This function is called differently in different databases:
- MySQL: SUBSTR( ), SUBSTRING( )
- Oracle: SUBSTR( )
- SQL Server: SUBSTRING( )
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.
SUBSTR() can be used in SELECT, WHERE, and ORDER BY clauses.
Below are some examples. Assume we have the following table:
SELECT SUBSTR (Store_Name, 3)
WHERE Store_Name = 'Los Angeles';
|SUBSTR (Store_Name, 3)|
SELECT SUBSTR (Store_Name, 2, 4)
WHERE Store_Name = 'San Diego';
|SUBSTR (Store_Name, 2, 4)|
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:
|New York||ew Y|
|Los Angeles||os A|
|San Diego||an D|
Based on the above, we can see that 'San Diego' would be first, followed by 'New York', 'Chicago', and finally 'Los Angeles.'
Next: SQL INSTR
|| Copyright © 2013 1keydata.com All Rights Reserved.