SUBSTRING Function
Returns a portion of a character string, starting at a specified position and of a specified length.
Syntax
SUBSTRING ( expression , start , length )
Parameters
| Parameter | Data Type | Description |
|---|---|---|
| expression | VARCHAR, NVARCHAR, CHAR, NCHAR, TEXT, NTEXT, VARCHAR(MAX), NVARCHAR(MAX) |
The character string expression to extract characters from. |
| start | INT |
An integer expression that specifies the starting position of the characters to extract. The first character in the string is position 1. Negative values for start will result in an error. |
| length | INT |
An integer expression that specifies the number of characters to extract. If length is negative, an error is returned. If the sum of start and length exceeds the number of characters available in the string, the characters up to the end of the string are returned. |
Return Type
Returns a character string of the same data type as expression.
Examples
Example 1: Basic Usage
Extract the substring 'World' from 'Hello World'.
SELECT SUBSTRING('Hello World', 7, 5);
-- Result: World
Example 2: Extracting until the end of the string
Extract characters from position 3 to the end of the string 'SQL Server'.
SELECT SUBSTRING('SQL Server', 3, 100); -- Length is greater than remaining characters
-- Result: L Server
Example 3: Using SUBSTRING with a table
Extract the first 3 characters from the 'ProductName' column in the 'Products' table.
SELECT ProductName, SUBSTRING(ProductName, 1, 3) AS Abbreviation
FROM Products;
Remarks
- If
startorlengthis NULL, NULL is returned. - If
startis greater than the length of the string, an empty string is returned. - This function is Unicode-enabled. Non-Unicode strings with a length of more than 4000 bytes are truncated.
SUBSTRINGis equivalent toSUBSTRin some other SQL dialects.
For information on other string functions, see SQL String Functions.