SQL SUBSTRING Function Use and Examples

Overview

The SUBSTRING function returns part of a string according to a start position and length provided.

Explanation

SQL Server SUBSTRING Syntax

SUBSTRING (expression, startPosition, length)

Parameters

• expression – Input string used to get a portion of the string
• startPosition – Position number used to start getting the substring
• length – Number of characters for the length of the substring

Simple Microsoft SQL SUBSTRING Function Example

The following example will start show the substring starting at position 1 for a length of 5.

SELECT SUBSTRING('Hello world',1,5) as msg

Using Negative Value for SUBSTRING Function

If the start position is negative integer (as opposed to a positive integer), the substring still works fine and starts at a negative position.

SELECT SUBSTRING('Hello world',-2,5) as msg

So, if we use -2, this will count backwards. The “H” is position 1, so we have 0, -1, -2 which gets us 3 positions to the left of the “H” and then we want a specified length of 5 positions, so we get 3 of nothing and then position 1 and 2 of “Hello world” which is “He”.

However, if the length is negative, an error message will be displayed as shown with the following query:

SELECT SUBSTRING('Hello world',2,-5) as msg

Here is the error.

Invalid length parameter passed to the substring function.

Using SUBSTRING with Varbinary Data Type

The following example is getting a substring of a varbinary(max) column.

SELECT SUBSTRING(LargePhoto,50,10) as msg
FROM [Production].[ProductPhoto]

Use SUBSTRING to Get Data After and Before a Character

The following example will show data before and after a space for a character string.

DECLARE @string varchar(50)='Hello world'
 
SELECT SUBSTRING(@string,1,CHARINDEX(' ',@string)) as firstpart,
       SUBSTRING(@string,CHARINDEX(' ',@string),LEN(@string)+1-CHARINDEX(' ',@string)) as secondpart

Use SUBSTRING to Break Apart Email Address

The following example will separate the name from the domain in an email address.

SELECT 
   EmailAddress,
   SUBSTRING(EmailAddress,1,CHARINDEX('@',EmailAddress)-1) as username,
   SUBSTRING(EmailAddress,CHARINDEX('@',EmailAddress)+1,LEN(EmailAddress)+1-CHARINDEX(' ',EmailAddress)) as domain
FROM [Person].[EmailAddress]

Use SUBSTRING to Parse File Path

The following example will get the drive, path, file and extension of a path.

DECLARE @fileNamePosition INT
DECLARE @ExtensionStartLeft INT
DECLARE @ExtensionStartRight INT
 
DECLARE @path varchar(300) = 'c:\Attachments\abc\demo.jpg'
 
SELECT  
    @fileNamePosition =    CHARINDEX('\',REVERSE(@path)),
    @ExtensionStartLeft =  CHARINDEX('.',REVERSE(@path)),
    @ExtensionStartRight = CHARINDEX('.',@path)
  
SELECT 
SUBSTRING(@path,1,3) as drive,
SUBSTRING(@path,LEN(@Path)-@fileNamePosition+2,@fileNamePosition-@ExtensionStartLeft-1) as filename,
SUBSTRING(@path,@ExtensionStartRight+1,@ExtensionStartLeft) as extension,
SUBSTRING(@path,1,LEN(@Path)-@ExtensionStartLeft) as path

Additional Information

• SQL Server SUBSTRING
• SQL String functions in SQL Server, Oracle and PostgreSQL
• SQL Server Substring Function Example with T-SQL, R and Python
• SQL Server Text Data Manipulation
• Parsing a URL with SQL Server Functions
• Name Parsing with SQL Server Functions and T-SQL Programming
• How to Extract URLs from HTML using Stored Procedure in SQL Server
• Related SQL Reference Tutorial Chapters
  • SQL CHARINDEX
  • SQL LEN
  • SQL REVERSE

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs.

• MSSQLTips Awards: Author of the Year Contender – 2015-2018, 2022, 2023 | Champion (100+ tips) – 2018