SQL Server SUBSTRING Function

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

SQL Server SUBSTRING() function overview #

The SUBSTRING() extracts a substring with a specified length starting from a location in an input string.

The following shows the syntax of the SUBSTRING() function:

SUBSTRING(input_string, start, length);
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• input_string can be a character, binary, text, ntext, or image expression.
• start is an integer that specifies the location where the returned substring starts. Note that the first character in the input_string is 1, not zero.
• length is a positive integer that specifies the number of characters of the substring to be returned. The SUBSTRING() function raises an error if the length is negative. If start + length > the length of input_string, the substring will begin at the start and include the remaining characters of the input_string.

SQL Server SUBSTRING() examples #

Let’s take some examples of using the SUBSTRING() function to understand how it works.

A) Using SUBSTRING() function with literal strings #

This example extracts a substring with the length of 6, starting from the fifth character, in the 'SQL Server SUBSTRING' string.

SELECT 
    SUBSTRING('SQL Server SUBSTRING', 5, 6) result;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

result
------
Server

(1 row affected)

B) Using  SUBSTRING() function with table columns #

We will use the sales.customers table in the sample database for the demonstration.

This example uses the SUBSTRING() function to extract domain from email addresses of customers:

SELECT 
    email, 
    SUBSTRING(
        email, 
        CHARINDEX('@', email)+1, 
        LEN(email)-CHARINDEX('@', email)
    ) domain
FROM 
    sales.customers
ORDER BY 
    email;
Code language: SQL (Structured Query Language) (sql)

The following picture shows the partial output:

How it works.

First, we used the CHARINDEX() function to search for the ‘@’ character in the email. The domain will start from the @ character plus one.

Then, we used the result of the CHARINDEX() function to determine:

• The starting location of the domain: CHARINDEX('@', email) + 1
• The length of the domain: LEN(email)-CHARINDEX('@', email)

To count the number of emails per domain, you can use the following query:

SELECT 
    SUBSTRING(
        email, 
        CHARINDEX('@', email)+1, 
        LEN(email)-CHARINDEX('@', email)
    ) domain,
    COUNT(email) domain_count
FROM 
    sales.customers
GROUP BY
    SUBSTRING(
            email, 
            CHARINDEX('@', email)+1, 
            LEN(email)-CHARINDEX('@', email)
        );
Code language: SQL (Structured Query Language) (sql)

Here is the output:

In this tutorial, you have learned how to used the SQL Server SUBSTRING() function to extract characters from a string.