Home > Development > Functions > String Functions > SUBSTRING

SUBSTRING (Transact-SQL)

Returns a part of a character string with the specified length from the specified start position.

Syntax

SUBSTRING ( { expression,start,length } )

Parameters

Parameter Description Data Type
expression The expression that returns a character string. The data type must be a character data type. varchar, nvarchar, char, nchar, text, ntext, varchar(max), nvarchar(max), image, varbinary, binary, varbinary(max).
start An integer expression that specifies the starting position of the characters to be returned. The first character in expression is position 1. int
length An integer expression that specifies the number of characters to be returned. int

Return Type

Returns character strings if expression is a character string data type. Returns binary strings if expression is a binary string data type.

Examples

Example 1: Basic Usage

This example extracts a substring from a given string.

SELECT SUBSTRING('SQL Server Functions', 5, 6);
-- Returns 'Server'

Example 2: Using Variables

This example demonstrates using variables for the start and length parameters.

DECLARE @myString VARCHAR(50) = 'Microsoft SQL Server';
DECLARE @startIndex INT = 11;
DECLARE @numChars INT = 5;

SELECT SUBSTRING(@myString, @startIndex, @numChars);
-- Returns 'SQL S'

Example 3: Extracting from a Table Column

This example extracts the first 10 characters from the ProductNumber column of a sample table.

SELECT SUBSTRING(ProductName, 1, 10) AS ShortProductName
FROM Production.Product;
-- Example output might show truncated product names

Notes