SQL SUBSTRING Code Examples and Usage

Problem

One common problem that Microsoft SQL Server Developers face is the need to extract a portion of a string from a larger text field to display the information in a more concise format or perform calculations/analysis on a specific substring of the data. How do you extract only a specific portion of the string in SQL Server?

This tutorial will discuss the SQL Server SUBSTRING function with various T-SQL examples.

Solution

The SQL Server SUBSTRING function extracts a portion of a string based on its starting position and length. The syntax for the SUBSTRING function is as follows:

SUBSTRING (input_string, start_position, length)

Here’s what each of the parameters means:

• input_string: string you want to extract a substring from
• start_position: position of input_string you want to begin extracting the substring (1 is the first position of the string)
• length: number of characters you want to extract from the input_string, starting from the start_position.

The SUBSTRING function returns the following data types based on the input data type:

Following are some examples of using the SQL Server SUBSTRING function.

Extracting a Substring from a String

Here is a basic example.

SELECT SUBSTRING('MSSQLTIPS.COM', 1, 9)

The output of this query is “MSSQLTIPS” because the SUBSTRING function is extracting a substring that starts at position 1 in the input string and has a length of 9 characters.

Extract Substring from a Column in a Table

In the following example, we extract a substring from the column [FirstName] of the [Person].[Person] table with a specified start position and string length parameter.

SELECT SUBSTRING(FirstName, 1, 3) FROM AdventureWorks2019.Person.Person

The output returns the first three characters of the FirstName column for each row from the Person table.

Extracting a Substring Using Variable Values

We can use variables to define the starting position and string length and use those variables in the SUBSTRING function as shown below.

DEDECLARE @string VARCHAR(50) = 'Hello, Read useful articles on MSSQLTIPS.com' -- original string
DECLARE @start INT = 7DECLARE @length INT = 34
SELECT SUBSTRING(@string, @start, @length)

In this example, we’ve declared variables @string, @start and @length and assigned values for each variable. Then we use them as parameters in the SUBSTRING function to extract a substring from the @string variable.

Extracting the Last Few Characters from a String

Suppose we have a string and our requirement is to extract the last few characters from it. For this purpose, we can use the LEN() function along with the SUBSTRING() function. The LEN() function returns the string length.

DECLARE @string VARCHAR(50) = 'Hello, Read useful articles on MSSQLTIPS.com'
DECLARE @substring varchar(50) = 'MSSQLTIPS.com'
SELECT SUBSTRING(@string, LEN(@string) - LEN(@substring), LEN(@substring)+1)

Extracting the Middle Name from a Full Name

SuSuppose your SQL database has a table with a column called FullName that contains the full name of each employee (first, middle, and last names separated by spaces). To extract the middle name for each employee, you can use the following query with the help of CHARINDEX which is used to find the position of a string in a string.

The query below looks for the first space and the second space in the FullName to determine the position of the MiddleName.

SELECT SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, 
       CHARINDEX(' ', FullName, CHARINDEX(' ', FullName) + 1) - CHARINDEX(' ', FullName) - 1) AS MiddleName
FROM Employees

Extracting a Portion of a String Based on a Pattern

Suppose you have a string column in a table that contains phone numbers in the format “(XXX) XXX-XXXX”. To extract only the area code, you can use the SUBSTRING function along with the PATINDEX function to search for a pattern.

In the example below, we look for a pattern that has an opening parenthesis three numbers and a closing parentheses and use these to determine the starting position.

Let’s create a table for mobile_numbers, insert sample records and fetch the data using the SUBSTRING function.

CREATE TABLE mobile_numbers (
  id INT PRIMARY KEY,
  phone_number VARCHAR(14)
);
INSERT INTO mobile_numbers (id, phone_number) VALUES
  (1, '(123) 456-7890'),
  (2, '(555) 555-1212'),
  (3, '(999) 867-5309');
 
SELECT SUBSTRING(phone_number, PATINDEX('(%[0-9][0-9][0-9])%', phone_number) + 1, 3) AS area_code
FROM mobile_numbers

It will return a column containing only the area codes of each phone number.

Truncating a String to a Specific Length with the SQL SUBSTRING Function

Suppose you have a string column in a table that contains product descriptions that are sometimes longer than the desired maximum length of 50 characters. To truncate the description to 50 characters (if necessary), you can use the SUBSTRING function along with the CASE statement to check if the length is greater than 50:p>

CREATE TABLE Comments (
  id INT PRIMARY KEY,
  description VARCHAR(100)
);
INSERT INTO Comments (id, description) VALUES
  (1, 'Here is a short description for the product.'),
  (2, 'This is the full description for the product that you want to purchase.'),
  (3, 'This description is within the character limit.');
SELECT
    CASE WHEN LEN(description) > 50 THEN SUBSTRING(description, 1, 50) + '...' 
    ELSE description 
    END AS truncated_description
FROM Comments;

It will return a column containing the full description for descriptions less than or equal to 50 characters in length and a truncated description with “…” appended to the end for descriptions longer than 50 characters.

Use Substring to Extract Domain Name from an Email

Suppose you have a string column in a table that contains email addresses in the format “username@domain.com”. To extract only the domain name (i.e., the part after the @ symbol), you can use the SUBSTRING function along with the CHARINDEX function to locate the @ symbol and extract the substring that follows it.

The following code creates a table [EmailAddress], inserts a few sample email addresses and fetches the domain names using the SUBSTRING function.

CREATE TABLE EmailAddress (
  id INT PRIMARY KEY,
  email VARCHAR(50)
);
INSERT INTO EmailAddress (id, email) VALUES
  (1, 'johndoe@example.com'),
  (2, 'janedoe@gmail.com'),
  (3, 'johnsmith@outlook.com');
SELECT SUBSTRING(email, CHARINDEX('@', email) + 1, LEN(email)) AS domain
FROM EmailAddress;

This will return a column containing only the domain names of each email address in the table.

Extracting a Substring with a Fixed Width

Suppose you have a string column in a table that contains Social Security Numbers in the format “XXX-XX-XXXX”. To extract only the middle portion of the Social Security Number (i.e., the part between the first and last dash), you can use the SUBSTRING function along with the REPLACE function to remove the first and last dashes and then extract the substring using a fixed width:

CREATE TABLE ssn_table (
  id INT PRIMARY KEY,
  ssn VARCHAR(11)
);
INSERT INTO ssn_table (id, ssn) VALUES
  (1, '123-45-6789'),
  (2, '987-65-4321'),
  (3, '555-55-5555');
SELECT SUBSTRING(REPLACE(ssn, '-', ''), 4, 2) AS middle_digits
FROM ssn_table

This will return a column containing only the middle two digits of each Social Security Number in the table.

Extracting a Substring Using a Computed Starting Position

Suppose you have a string column in a table that contains file paths in the format “C:\path\to\file.txt”. To extract only the file name (i.e., the part after the last backslash), you can use the SUBSTRING function along with the REVERSE function to return the position of the last backslash and then calculate the starting position of the substring:

CREATE TABLE folder_paths (
  id INT PRIMARY KEY,
  path VARCHAR(100)
);
INSERT INTO folder_paths (id, path) VALUES
  (1, 'C:\path\to\file.txt'),
  (2, 'D:\documents\myfiles\resume.docx'),
  (3, 'E:\media\videos\SampleVideo.mp4');
 
SELECT SUBSTRING(path, LEN(path) - CHARINDEX('\', REVERSE(path)) + 2, LEN(path)) AS file_name
FROM folder_paths

This will return a column containing only the file names of each file path in the table. Note: The starting position is calculated by subtracting the position of the last backslash from the total string length and then adding 2 (one to account for the backslash itself and one to shift the position to the first character of the file name).

Summary

In conclusion, SUBSTRING is a beneficial function in SQL Server that allows us to extract a portion of a string based on a specific starting position and length. It can manipulate and transform string data in various ways, such as removing unwanted characters, extracting meaningful information, and formatting data for specific purposes. By understanding how to use SUBSTRING effectively, we can improve the efficiency and accuracy of our SQL queries and make our database operations more streamlined and effective.

Next Steps

• Read these other items about SQL Server String Functions.