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
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.
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
Related Articles
- 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
Last Update: 1/24/2022
