SQL Server SUBSTRING() Function

 The SUBSTRING() function in SQL Server is used to extract a portion of a string from a given string (or column) starting at a specified position and for a specified length.

Syntax:

SUBSTRING(expression, start, length)

• expression: The string or column from which to extract the substring.
• start: The starting position for extraction (1-based index). If start is greater than the length of the string, it will return an empty string.
• length: The number of characters to return from the start position. If the length is greater than the remaining characters in the string, it will return all the characters from the start position.

Example 1: Basic usage

SELECT SUBSTRING('Hello, World!', 1, 5) AS SubstringResult;

This query returns "Hello" because it starts at position 1 and extracts 5 characters.

Example 2: Extracting from the middle of the string

SELECT SUBSTRING('Hello, World!', 8, 5) AS SubstringResult;

This query returns "World" because it starts at position 8 (the first "W") and extracts 5 characters.

Example 3: Handling start position greater than string length

SELECT SUBSTRING('Hello', 10, 3) AS SubstringResult;

This query returns an empty string ("") because the starting position (10) is beyond the length of the string.

Example 4: Extracting a substring with a length greater than available characters

SELECT SUBSTRING('SQL Server', 5, 20) AS SubstringResult;

This query returns "Server" because the length (20) is more than the remaining characters after position 5, so it extracts all available characters from that position.

Notes:

• The start index in SQL Server is 1-based, meaning the first character is at position 1, not 0.
• If the start position is less than 1, SQL Server will return an error.
• If length is less than or equal to 0, SQL Server will return an empty string.

Example with a column:

SELECT SUBSTRING(ProductName, 1, 4) AS ShortName
FROM Products;

This query extracts the first 4 characters of each ProductName from the Products table.

The SUBSTRING() function is helpful for manipulating strings and performing partial text extraction.