SQL Server SUBSTRING() Function

In SQL Server, the SUBSTRING() function returns a part of a given string. The given string can be of character, binary, text, or image type.

SUBSTRING(expression, start, length)

Parameters

expression: The character, binary, text, ntext, or image type.

Start: An integer or bigint. It specifies the position where the returned substring starts. The numbering starts with 1 and not 0; i.e, the first character in the substring is numbered 1 and so on.

Length: A positive integer. It specifies the number of characters of the expression that will be returned starting from the start position. If length is negative, an error is generated.

Return Value

Returns string of varchar type, if the given expression is char, varchar, or text.

Returns nvarchar type, if the given expression is nchar, nvarchar, or ntext.

Returns varbinary, type if the given expression is binary, varbinary, or image.

Example 1:

In the following example a part of the given sentence 'Hello World' is returned.

Example: SUBSTRING()

SELECT SUBSTRING('Hello World', 7, 5) AS Result;

Example 3:

In the following example, the SUBSTRING() function is used on the FirstName column of the Employee table to return a substring with 4 characters.

Example: SUBSTRING()

SELECT SUBSTRING(FirstName, 1,4) AS NickName FROM Employee;

Example 4:

In the following example, a negative integer is passed as length to the SUBSTRING() function which will throw an error.

Example: SUBSTRING()

SELECT SUBSTRING('Hello World', 1, -5) AS Result