SQL SUBSTRING Function Use and Examples

By: Daniel Calbimonte

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

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

substring t-sql negative start parameter

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]

t-sql break apart emai address with substring function

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

sql server get drive, filename, extension

SQL Server SUBSTRING Function

MENU

By: Daniel Calbimonte

SQL Server SUBSTRING Syntax

Parameters

Simple Microsoft SQL SUBSTRING Function Example

Using Negative Value for SUBSTRING Function

Using SUBSTRING with Varbinary Data Type

Use SUBSTRING to Get Data After and Before a Character

Use SUBSTRING to Break Apart Email Address

Use SUBSTRING to Parse File Path

Related Articles

<< Previous

Next >>

Comments For This Article