SQL > SQL String Functions > Substring

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:

Table Geography

Region_NameStore_Name
EastChicago
EastNew York
WestLos Angeles
WestSan Diego

Example 1

SELECT SUBSTR (Store_Name, 3)
FROM Geography
WHERE Store_Name = 'Los Angeles';

Result:

SUBSTR (Store_Name, 3)
s Angeles

Example 2

SELECT SUBSTR (Store_Name, 2, 4)
FROM Geography
WHERE Store_Name = 'San Diego';

Result:

SUBSTR (Store_Name, 2, 4)
an D

Example 3

SELECT Store_Name
FROM Geography
ORDER BY SUBSTR (Store_Name, 2, 4);

Result:

Store_Name
San Diego
New York
Chicago
Los Angeles

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:

Store_NameSUBSTR(Store_Name,2,4)
Chicagohica
New Yorkew Y
Los Angelesos A
San Diegoan 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 © 2014   1keydata.com   All Rights Reserved.     Privacy Policy     About   Contact