SQL Substring Function

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 October 12, 2024.

AdBlock Detected!

Our website is made possible by displaying ads to our visitors. Please supporting us by whitelisting our website.