SQL Server SUBSTRING Function


By:

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
simple example

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".

substring t-sql negative start parameter

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]
substring to varbinary max

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
Get data after and before a space

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

Related Articles


Last Update: 1/24/2022




Comments For This Article





download














get free sql tips
agree to terms