| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
SUBSTRING (Transact-SQL) | The SUBSTRING function returns a portion of a specified character, binary, text, or image expression. | MikeRayMSFT | mikeray | randolphwest | 02/10/2025 | sql | t-sql | reference |
|
|
| >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric |
[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw]
Returns part of a character, binary, text, or image expression in [!INCLUDE ssNoVersion].
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
Syntax for [!INCLUDE ssnoversion-md].
SUBSTRING ( expression , start , length ) Syntax for [!INCLUDE ssazure-sqldb], [!INCLUDE ssazuremi-md], [!INCLUDE ssazuresynapse-md], [!INCLUDE ssazurepdw_md], and [!INCLUDE fabric-dw] and [!INCLUDE fabric-se] in [!INCLUDE fabric].
SUBSTRING ( expression , start [ , length ] ) A character, binary, text, ntext, or imageexpression.
An integer or bigint expression that specifies where the returned characters start. (The numbering is 1 based, meaning that the first character in the expression is 1). If start is less than 1, the returned expression begins at the first character that is specified in expression. In this case, the number of characters that are returned is the largest value of either the sum of start + length - 1, or 0. If start is greater than the number of characters in the value expression, a zero-length expression is returned.
A positive integer or bigint expression that specifies how many characters of the expression are returned. If length is negative, an error is generated and the statement is terminated. If the sum of start and length is greater than the number of characters in expression, the whole value expression beginning at start is returned. If length is omitted, all characters from the start position to the end of the expression is returned.
You can use substring with an optional length argument in [!INCLUDE ssazure-sqldb], [!INCLUDE ssazuremi-md], [!INCLUDE ssazuresynapse-md], [!INCLUDE ssazurepdw_md], and [!INCLUDE fabric-dw] and [!INCLUDE fabric-se] in [!INCLUDE fabric]. However, if you use NULL for length, SUBSTRING returns NULL. Review E. Use SUBSTRING with optional length argument for an example.
Returns character data if expression is one of the supported character data types. Returns binary data if expression is one of the supported binary data types. The returned string is the same type as the specified expression with the exceptions shown in the table.
| Specified expression | Return type |
|---|---|
| char / varchar / text | varchar |
| nchar / nvarchar / ntext | nvarchar |
| binary / varbinary / image | varbinary |
The values for start and length must be specified in number of characters for ntext, char, or varchar data types and bytes for text, image, binary, or varbinary data types.
The expression must be varchar(max) or varbinary(max) when the start or length contains a value larger than 2,147,483,647.
When you use supplementary character (SC) collations, both start and length count each surrogate pair in expression as a single character. For more information, see Collation and Unicode support.
The following example shows how to return only a part of a character string. From the sys.databases table, this query returns the system database names in the first column, the first letter of the database in the second column, and the third and fourth characters in the final column.
SELECT name, SUBSTRING(name, 1, 1) AS Initial, SUBSTRING(name, 3, 2) AS ThirdAndFourthCharacters FROMsys.databasesWHERE database_id <5;[!INCLUDE ssResult]
| name | Initial | ThirdAndFourthCharacters |
|---|---|---|
master | m | st |
tempdb | t | mp |
model | m | de |
msdb | m | db |
To display the second, third, and fourth characters of the string constant abcdef, use the following query.
SELECTSUBSTRING('abcdef', 2, 3) AS x;[!INCLUDE ssResult]
x ---------- bcd Note
To run the following examples, you must install the pubs database.
The following example shows how to return the first 10 characters from each of a text and image data column in the pub_info table of the pubs database. text data is returned as varchar, and image data is returned as varbinary.
USE pubs; GO SELECT pub_id, SUBSTRING(logo, 1, 10) AS logo, SUBSTRING(pr_info, 1, 10) AS pr_info FROM pub_info WHERE pub_id ='1756';[!INCLUDE ssResult]
pub_id logo pr_info ------ ---------------------- ---------- 1756 0x474946383961E3002500 This is sa The following example shows the effect of SUBSTRING on both text and ntext data. First, this example creates a new table in the pubs database named npub_info. Second, the example creates the pr_info column in the npub_info table from the first 80 characters of the pub_info.pr_info column and adds an ü as the first character. Lastly, an INNER JOIN retrieves all publisher identification numbers and the SUBSTRING of both the text and ntext publisher information columns.
IF EXISTS (SELECT table_name FROMINFORMATION_SCHEMA.TABLESWHERE table_name ='npub_info') DROPTABLE npub_info; GO -- Create npub_info table in pubs database. Borrowed from instpubs.sql. USE pubs; GO CREATETABLEnpub_info ( pub_id CHAR (4) NOT NULLFOREIGN KEYREFERENCES publishers (pub_id) CONSTRAINT UPKCL_npubinfo PRIMARY KEY CLUSTERED, pr_info NTEXT NULL ); GO -- Fill the pr_info column in npub_info with international data. RAISERROR ('Now at the inserts to pub_info...', 0, 1); GO INSERT npub_info VALUES ('0736', N'üThis is sample text data for New Moon Books, publisher 0736 in the pubs database'), ('0877', N'üThis is sample text data for Binnet & Hardley, publisher 0877 in the pubs databa'), ('1389', N'üThis is sample text data for Algodata Infosystems, publisher 1389 in the pubs da'), ('9952', N'üThis is sample text data for Scootney Books, publisher 9952 in the pubs database'), ('1622', N'üThis is sample text data for Five Lakes Publishing, publisher 1622 in the pubs d'), ('1756', N'üThis is sample text data for Ramona Publishers, publisher 1756 in the pubs datab'), ('9901', N'üThis is sample text data for GGG&G, publisher 9901 in the pubs database. GGG&G i'), ('9999', N'üThis is sample text data for Lucerne Publishing, publisher 9999 in the pubs data'); GO -- Join between npub_info and pub_info on pub_id.SELECTpr.pub_id, SUBSTRING(pr.pr_info, 1, 35) AS pr_info, SUBSTRING(npr.pr_info, 1, 35) AS npr_info FROM pub_info AS pr INNER JOIN npub_info AS npr ONpr.pub_id=npr.pub_idORDER BYpr.pub_idASC;Examples: [!INCLUDE ssazuresynapse-md] and [!INCLUDE ssPDW]
The following example shows how to return only a part of a character string. From the dbo.DimEmployee table, this query returns the family name in one column with only the first initial in the second column.
-- Uses AdventureWorksSELECT LastName, SUBSTRING(FirstName, 1, 1) AS Initial FROMdbo.DimEmployeeWHERE LastName LIKE'Bar%'ORDER BY LastName;[!INCLUDE ssResult]
LastName Initial -------------------- ------- Barbariol A Barber D Barreto de Mattos P The following example shows how to return the second, third, and fourth characters of the string constant abcdef.
USE ssawPDW; SELECT TOP 1SUBSTRING('abcdef', 2, 3) AS x FROMdbo.DimCustomer;[!INCLUDE ssResult]
x ----- bcd SELECTSUBSTRING('123abc', 4, NULL) AS [NULL length];[!INCLUDE ssResult]
NULL length ----------- NULL Applies to: [!INCLUDE ssazure-sqldb], [!INCLUDE ssazuremi-md], [!INCLUDE ssazuresynapse-md], [!INCLUDE ssazurepdw_md], and [!INCLUDE fabric-dw] and [!INCLUDE fabric-se] in [!INCLUDE fabric]
The following example shows how to return only a part of a character string from a given start position. Since the length argument isn't provided, the length defaults to return the remaining characters in the string.
SELECTSUBSTRING('123abc', 4) AS y;[!INCLUDE ssResult]
y ----- abc F. Use SUBSTRING without a length argument to find replacement parts in AdventureWorks2022 inventory
USE AdventureWorks2022; GO SELECT [ProductDescriptionID], [Description], SUBSTRING([Description], LEN('Replacement') +1) AS [Replacement-Part] FROM [Production].[ProductDescription] WHERE [Description] LIKE'Replacement%';[!INCLUDE ssResult]
| ProductDescriptionID | Description | Replacement-Part |
|---|---|---|
| 686 | Replacement mountain wheel for entry-level rider. | mountain wheel for entry-level rider. |
| 687 | Replacement mountain wheel for the casual to serious rider. | mountain wheel for the casual to serious rider. |
| 689 | Replacement road front wheel for entry-level cyclist. | road front wheel for entry-level cyclist. |
| 867 | Replacement rear mountain wheel for entry-level rider. | rear mountain wheel for entry-level rider. |
| 868 | Replacement rear mountain wheel for the casual to serious rider. | rear mountain wheel for the casual to serious rider. |
| 870 | Replacement rear wheel for entry-level cyclist. | rear wheel for entry-level cyclist. |
| 1981 | Replacement mountain wheel for entry-level rider. | mountain wheel for entry-level rider. |
| 1987 | Replacement mountain wheel for the casual to serious rider. | mountain wheel for the casual to serious rider. |
| 1999 | Replacement road rear wheel for entry-level cyclist. | road rear wheel for entry-level cyclist. |
