SQL SUBSTRING: Extract a Substring from a String

Summary: in this tutorial, you will learn how to use the SQL SUBSTRING function to extract a substring from a string.

Introduction to the SQL SUBSTRING function

The SUBSTRING function extracts a substring that starts at a specified position with a given length.

The following illustrates the syntax of the SUBSTRING function.

SUBSTRING(source_string, position, length);Code language: SQL (Structured Query Language) (sql)

The SUBSTRING function accepts three arguments:

The source_string is the string from which you want to extract the substring.
The position is the starting position where the substring begins. The first position of the string is one (1).
The length is the length of the substring. The length argument is optional.

Most relational database systems implement the SUBSTRING function with the same functionality.

SQL SUBSTRING function examples

The following example returns a substring starting at position 1 with length 3.

SELECT SUBSTRING('SQLTutorial.org',1,3);Code language: SQL (Structured Query Language) (sql)

substring
-----------
 SQL
(1 row)Code language: SQL (Structured Query Language) (sql)

The following statement returns a substring starting at position 4 with length 8.

SELECT SUBSTRING('SQLTutorial.org',4,8);Code language: SQL (Structured Query Language) (sql)

 substring
-----------
 Tutorial
(1 row)
Code language: SQL (Structured Query Language) (sql)

The following statement uses the POSITION function to return the position of the dot character (.) in the string.

The result of the POSITION function is passed to the SUBSTRING function to find the extension of a domain:

SELECT 
    SUBSTRING('SQLTutorial.org',
        POSITION('.' IN 'SQLTutorial.org'));Code language: SQL (Structured Query Language) (sql)

substring
-----------
 .org
(1 row)Code language: SQL (Structured Query Language) (sql)

See the following employees table in the sample database.

The following query uses the SUBSTRING function to extract the first characters of the employee’s first names (initials) and group employees by the initials:

SELECT 
    SUBSTRING(first_name, 1, 1) initial, 
    COUNT(employee_id)
FROM
    employees
GROUP BY initial;Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the SQL SUBSTRING function to extract a substring from a string.

Was this tutorial helpful ?