SQL Server SUBSTRING(): A Complete Guide to Extracting Strings

The SUBSTRING() function in SQL Server lets you pull out specific portions of a string. Whether you’re cleaning data, formatting output, or parsing complex text fields, this function is one you’ll use constantly.

If you’ve ever needed to extract an area code from a phone number, grab the domain from an email address, or parse product codes into their component parts, SUBSTRING() is your go-to tool. It’s pretty straightforward, and once you understand how it works, you’ll find yourself using it all the time.

What Does `SUBSTRING()` Do?

SUBSTRING() extracts a specific number of characters from a string, starting at a position you specify. The beauty of this function is its flexibility. You can extract a fixed number of characters if you know exactly how many you need, or you can grab everything from a certain position to the end of the string by omitting the length parameter entirely.

This makes it perfect for both predictable patterns (like extracting the first three characters of a product code) and dynamic situations (like extracting everything after a delimiter that might appear at different positions in different strings). The function works with any text data type (VARCHAR, NVARCHAR, CHAR, etc), and easily handles both regular and Unicode text.

`SUBSTRING()` Syntax

The syntax goes like this:

SUBSTRING(string, start [, length])

Parameters:

string: The text you want to extract from (can be a column, variable, or literal string)
start: The position where extraction begins (1 = first character)
length: (Optional) How many characters to extract. If omitted, extracts from start position to the end of the string

Basic `SUBSTRING()` Examples

Here are some simple examples to get you started with the basics.

Extract First 5 Characters

SELECT SUBSTRING('Pizza Party', 1, 5);

Result:

Pizza

Extract from Middle of String

SELECT SUBSTRING('Pizza Party', 4, 5);

Result:

za Pa

Extract Until End of String

Here are two ways to get everything from a position to the end.

Method 1 is to omit the length parameter:

SELECT SUBSTRING('Code Ninja', 6);

Result:

Ninja

Method 2 is to use a large number for the length:

SELECT SUBSTRING('Code Ninja', 6, 100);

Result:

Ninja

Bear in mind that when using Method 2, the third argument needs to be big enough that it goes past the end of the text. You’re almost always better off using Method 1 – that way you won’t need to worry about whether or not you’ll inadvertently cut off some text.

Real-World `SUBSTRING()` Use Cases

Now let’s look at how you’d actually use SUBSTRING() in production code. First, let’s create some tables and populate them:

-- =============================================
-- Create Customers Table
-- =============================================
CREATE TABLE dbo.Customers (
    CustomerID INT PRIMARY KEY IDENTITY(1,1),
    CustomerName VARCHAR(100) NOT NULL,
    Email VARCHAR(100),
    PhoneNumber VARCHAR(15)
);

-- Insert sample data into Customers
INSERT INTO dbo.Customers (CustomerName, Email, PhoneNumber) VALUES
('Sarah Martinez', '[email protected]', '415-555-0192'),
('Michael Chen', '[email protected]', '650-555-0847'),
('Jennifer Wong', '[email protected]', '408-555-2341'),
('Robert Anderson', '[email protected]', '925-555-7623'),
('Lisa Thompson', '[email protected]', '510-555-4982'),
('David Park', '[email protected]', '415-555-3156'),
('Emily Rodriguez', '[email protected]', '650-555-8901'),
('James Wilson', '[email protected]', '408-555-6734'),
('Maria Garcia', '[email protected]', '925-555-1289'),
('Christopher Lee', '[email protected]', '510-555-5647'),
('Amanda Brooks', '[email protected]', '415-555-9012'),
('Daniel Kim', '[email protected]', '650-555-2367'),
('Jessica Taylor', '[email protected]', '408-555-8834'),
('Matthew Davis', '[email protected]', '925-555-4501'),
('Ashley Johnson', '[email protected]', '510-555-7123');

-- =============================================
-- Create Products Table
-- =============================================
CREATE TABLE dbo.Products (
    ProductID INT PRIMARY KEY IDENTITY(1,1),
    ProductCode VARCHAR(20) NOT NULL,
    ProductName VARCHAR(100) NOT NULL,
    Description VARCHAR(500)
);

-- Insert sample data into Products
INSERT INTO dbo.Products (ProductCode, ProductName, Description) VALUES
('EL2401', 'Wireless Keyboard', 'Bluetooth keyboard with 12-month battery life'),
('EL3502', 'USB-C Hub', 'Premium 7-port USB-C hub with power delivery'),
('HO1203', 'Standing Desk', 'Electric height-adjustable desk, holds 150 lbs'),
('HO2105', 'Ergonomic Chair', 'Mesh back office chair with lumbar support'),
('EL4801', 'Webcam HD Pro', '1080p webcam with auto-focus and noise cancellation'),
('OF0934', 'Notebook Set', 'Pack of 3 hardcover notebooks, 200 pages each'),
('EL5602', 'Monitor 27inch', '4K UHD monitor with HDR support'),
('HO3307', 'Desk Lamp LED', 'Adjustable LED desk lamp with USB charging port'),
('OF1245', 'File Cabinet', '3-drawer mobile file cabinet with lock'),
('EL6103', 'Mouse Wireless', 'Precision wireless mouse with programmable buttons'),
('OF2456', 'Whiteboard Large', '6x4 feet magnetic whiteboard with aluminum frame'),
('EL7204', 'Headphones Pro', 'Active noise cancelling over-ear headphones'),
('HO4508', 'Bookshelf Unit', '5-shelf bookcase, solid wood construction'),
('EL8305', 'Cable Organizer', 'Premium cable management box with power strip'),
('OF3667', 'Desk Organizer', 'Bamboo desk organizer with 6 compartments');

Now let’s go ahead and get some substrings from that data.

Extract Area Code from Phone Numbers

SELECT 
    PhoneNumber,
    SUBSTRING(PhoneNumber, 1, 3) AS AreaCode
FROM Customers;

Result:

PhoneNumber   AreaCode
------------  --------
415-555-0192  415     
650-555-0847  650     
408-555-2341  408     
925-555-7623  925     
510-555-4982  510     
415-555-3156  415     
650-555-8901  650     
408-555-6734  408     
925-555-1289  925     
510-555-5647  510     
415-555-9012  415     
650-555-2367  650     
408-555-8834  408     
925-555-4501  925     
510-555-7123  510

Get First Three Letters of Name

SELECT 
    CustomerName,
    SUBSTRING(CustomerName, 1, 3) AS ShortCode
FROM Customers;

Result:

CustomerName     ShortCode
---------------  ---------
Sarah Martinez   Sar      
Michael Chen     Mic      
Jennifer Wong    Jen      
Robert Anderson  Rob      
Lisa Thompson    Lis      
David Park       Dav      
Emily Rodriguez  Emi      
James Wilson     Jam      
Maria Garcia     Mar      
Christopher Lee  Chr      
Amanda Brooks    Ama      
Daniel Kim       Dan      
Jessica Taylor   Jes      
Matthew Davis    Mat      
Ashley Johnson   Ash

Parse Product Codes

SELECT 
    ProductCode,
    SUBSTRING(ProductCode, 1, 2) AS Category,
    SUBSTRING(ProductCode, 3, 4) AS SubCategory
FROM Products;

Result:

ProductCode  Category  SubCategory
-----------  --------  -----------
EL2401       EL        2401       
EL3502       EL        3502       
HO1203       HO        1203       
HO2105       HO        2105       
EL4801       EL        4801       
OF0934       OF        0934       
EL5602       EL        5602       
HO3307       HO        3307       
OF1245       OF        1245       
EL6103       EL        6103       
OF2456       OF        2456       
EL7204       EL        7204       
HO4508       HO        4508       
EL8305       EL        8305       
OF3667       OF        3667

`SUBSTRING()` with Other Functions

SUBSTRING() can be quite useful when you combine it with other string functions. This is where you can solve more complex text manipulation problems.

Combine with `CHARINDEX()` to Extract Dynamic Text

In this example, we find a delimiter and extract everything after it:

SELECT 
    Email,
    SUBSTRING(Email, CHARINDEX('@', Email) + 1) AS Domain
FROM Customers;

Result:

Email                     Domain        
------------------------  --------------
[email protected]  gmail.com     
[email protected]         outlook.com   
[email protected]         company.com   
[email protected]        yahoo.com     
[email protected]   gmail.com     
[email protected]        techcorp.com  
[email protected]       hotmail.com   
[email protected]        startup.io    
[email protected]    gmail.com     
[email protected]       consulting.com
[email protected]         design.co     
[email protected]    outlook.com   
[email protected]        agency.com    
[email protected]          gmail.com     
[email protected]        yahoo.com

Extract Text Between Delimiters

We can also extract everything between delimiters. For example if we have a comma-separated string of “Cortez, Marty, Dr.“, we can extract a value like this:

DECLARE @FullName VARCHAR(100) = 'Cortez, Marty, Dr.';

SELECT SUBSTRING(
    @FullName,
    CHARINDEX(',', @FullName) + 2,
    CHARINDEX(',', @FullName, CHARINDEX(',', @FullName) + 1) - CHARINDEX(',', @FullName) - 2
) AS FirstName;

Result:

Marty

Using `SUBSTRING()` with `PATINDEX()`

We combine SUBSTRING() with PATINDEX() to extract based on pattern matching:

DECLARE @Text VARCHAR(100) = 'Item costs $49.99 today';

SELECT 
    @Text AS OriginalText,
    PATINDEX('%[0-9]%', @Text) AS NumberStartsAt,
    SUBSTRING(@Text, PATINDEX('%[0-9]%', @Text), 5) AS ExtractedPrice;

Result:

OriginalText             NumberStartsAt  ExtractedPrice
-----------------------  --------------  --------------
Item costs $49.99 today  13              49.99

This can be useful when you know roughly how long the pattern is, but don’t know exactly where it starts.

Important Things to Know About `SUBSTRING()`

There are a few quirks and behaviors you should understand to avoid surprises when using SUBSTRING().

Positions Start at 1, Not 0

Unlike many programming languages, SQL Server uses 1-based indexing:

SELECT SUBSTRING('Coffee', 1, 1);

Result:

This returns C, because that corresponds with 1. If we were using zero-based indexing, it would’ve returned o.

Out-of-Bounds Behavior

If your start position is beyond the string length, you get an empty string:

SELECT SUBSTRING('Chickpeas', 50, 5) AS Result;

Result:

Result
------
      

1 row(s) returned

Negative or Zero Start Position

Starting at position 0 or negative numbers won’t error, but you might not get what you expect:

SELECT 
    SUBSTRING('Tacos > Burritos', 0, 3) AS "0, 3",
    SUBSTRING('Tacos > Burritos', -1, 5) AS "-1, 5";

Result:

0, 3  -1, 5
----  -----
Ta    Tac

It went back to a zero or negative starting point and counted from there, which meant that we didn’t extract the number of characters we specified in the second argument.

Works with Unicode (`NVARCHAR`)

SUBSTRING() handles Unicode characters well:

SELECT SUBSTRING(N'こんにちは', 1, 2);

Result:

こん

`SUBSTRING()` Performance Tips

Using SUBSTRING() carelessly in the wrong places can slow down your queries.

For better query performance:

Avoid using SUBSTRING() in WHERE clauses when possible, as it prevents index usage
Compute extracted values once in a CTE or derived table rather than multiple times
Consider computed columns if you frequently query the same substring

Here’s an example of a query that may benefit from a computed column:

SELECT * FROM Customers 
WHERE SUBSTRING(Email, CHARINDEX('@', Email) + 1, 100) = 'gmail.com';

If we’re frequently doing queries like this, we might benefit from creating a computed column that stores all email domains in a dedicated column specifically for this purpose:

ALTER TABLE Customers
ADD EmailDomain AS (
    CASE 
        WHEN CHARINDEX('@', Email) > 0 
        THEN SUBSTRING(Email, CHARINDEX('@', Email) + 1)
        ELSE NULL
    END
) PERSISTED;

We could create an index on this column to further speed things up:

CREATE INDEX IX_Customers_EmailDomain ON Customers(EmailDomain);

Now we can query the EmailDomain column directly:

SELECT * FROM Customers 
WHERE EmailDomain = 'gmail.com';

Result:

CustomerID  CustomerName    Email                     PhoneNumber   EmailDomain
----------  --------------  ------------------------  ------------  -----------
1           Sarah Martinez  [email protected]  415-555-0192  gmail.com  
5           Lisa Thompson   [email protected]   510-555-4982  gmail.com  
9           Maria Garcia    [email protected]    925-555-1289  gmail.com  
14          Matthew Davis   [email protected]          925-555-4501  gmail.com

`SUBSTRING()` Alternatives in SQL Server

Just because SUBSTRING() is designed to extract text, it doesn’t mean that it’s always going to be the best tool for the job. Here are some alternatives that might work better depending on what you’re trying to do.

`LEFT()` and `RIGHT()`

For extracting from the beginning or end:

SELECT 
    LEFT('Debugging is twice as hard', 9) AS "LEFT 9",
    RIGHT('Debugging is twice as hard', 4) AS "RIGHT 4";

Result:

LEFT 9     RIGHT 4
---------  -------
Debugging  hard

`STRING_SPLIT()` (SQL Server 2016+)

For splitting delimited strings:

SELECT value FROM STRING_SPLIT('apple,banana,orange', ',');

Result:

value 
------
apple 
banana
orange

`TRIM()`, `LTRIM()`, `RTRIM()`

For removing characters from ends:

DECLARE @Fruit VARCHAR(10) = '  Apple  ';
SELECT 
    'Orange' || @Fruit || 'Banana' AS "Untrimmed",
    'Orange' || TRIM(@Fruit) || 'Banana' AS "Trimmed";

Result:

Untrimmed              Trimmed          
---------------------  -----------------
Orange  Apple  Banana  OrangeAppleBanana

Quick Reference

Task	Code Example
Extract first N characters	`SUBSTRING(text, 1, N)`
Extract from position X to end	`SUBSTRING(text, X)`
Extract middle portion	`SUBSTRING(text, start, length)`
Extract after delimiter	`SUBSTRING(text, CHARINDEX(delim, text) + 1)`

What Does SUBSTRING() Do?

SUBSTRING() Syntax

Basic SUBSTRING() Examples

Extract First 5 Characters

Extract from Middle of String

Extract Until End of String

Real-World SUBSTRING() Use Cases

Extract Area Code from Phone Numbers

Get First Three Letters of Name

Parse Product Codes

SUBSTRING() with Other Functions

Combine with CHARINDEX() to Extract Dynamic Text

Extract Text Between Delimiters

Using SUBSTRING() with PATINDEX()

Important Things to Know About SUBSTRING()

Positions Start at 1, Not 0

Out-of-Bounds Behavior

Negative or Zero Start Position

Works with Unicode (NVARCHAR)

SUBSTRING() Performance Tips

SUBSTRING() Alternatives in SQL Server

LEFT() and RIGHT()

STRING_SPLIT() (SQL Server 2016+)

TRIM(), LTRIM(), RTRIM()

Quick Reference

What Does `SUBSTRING()` Do?

`SUBSTRING()` Syntax

Basic `SUBSTRING()` Examples

Real-World `SUBSTRING()` Use Cases

`SUBSTRING()` with Other Functions

Combine with `CHARINDEX()` to Extract Dynamic Text

Using `SUBSTRING()` with `PATINDEX()`

Important Things to Know About `SUBSTRING()`

Works with Unicode (`NVARCHAR`)

`SUBSTRING()` Performance Tips

`SUBSTRING()` Alternatives in SQL Server

`LEFT()` and `RIGHT()`

`STRING_SPLIT()` (SQL Server 2016+)

`TRIM()`, `LTRIM()`, `RTRIM()`