SQL > SQL String Functions > Substring

The Substring function in SQL is used to return a portion of the string. Each database provides its own way(s) of doing this:

  • MySQL: SUBSTR( ), SUBSTRING( )
  • Oracle: SUBSTR( )
  • SQL Server: SUBSTRING( )

Syntax

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.

Examples

We use the following table for our examples.

Table Geography

 Region_Name  Store_Name 
 East  Chicago 
 East  New York 
 West  Los Angeles 
 West  San 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 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:

Store_Name SUBSTR(Store_Name,2,4)
Chicago hica
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

This page was last updated on June 05, 2022.




Copyright © 2022   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact