SQL Substring Function

Create A Website SQL Data Warehousing CSS Database Normalization

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.

SQL Substring Function

Syntax

Examples

Example 1

Example 2

Example 3

AdBlock Detected!