In SQL Server, you can use the T-SQL SUBSTRING() function to return a substring from a given string.

You can use SUBSTRING() to return parts of a character, binary, text, or image expression.

Syntax

The syntax goes like this:

SUBSTRING ( expression ,start , length )

This means that you provide 3 arguments. The first argument is the expression (or string) with which you want to extract the substring from. The second argument specifies where to start, and the third argument specifies how long the substring should be.

Example

Here’s an example:

SELECT SUBSTRING('Cats and dogs', 10, 3);

Result:

dog

In this example, we start at the 10th character and return 3 characters.

Zero and Negative Starting Points

If you use 0 or a negative value as the starting position, it will start at that zero or negative position. Therefore, this will affect how many characters are actually returned.

Example:

SELECT SUBSTRING('Cats and dogs', 0, 3);

Result:

Ca

Only two characters are returned. This is because we specified 3, but we started at 0. The numbering is 1 based, therefore 0 reduces the number of characters returned by 1.