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