Returns part of a character, binary, text, or image expression. For more information about the valid SQL Server 2005 data types that can be used with this function, see Data Types (Transact-SQL).
SUBSTRING ( expression ,start , length )
Is a character string, binary string, text, image, a column, or an expression that includes a column. Do not use expressions that include aggregate functions.
Is an integer that specifies where the substring starts. start can be of type bigint.
Is a positive integer that specifies how many characters or bytes of the expression will be returned. If lengthis negative, an error is returned. lengthcan be of type bigint.
Because start and length specify the number of bytes, when SUBSTRING is used on the text data type with DBCS collation, such as kanji, it may cause a split in characters at the beginning or the end of the result. This behavior is consistent with the way in which READTEXT handles DBCS. Therefore we recommend that you use ntext instead of text for DBCS characters. Another alternative we recommend is to use the varchar(max) data type because it does not split characters for DBCS collations.
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|
Offsets (start and length) that use the ntext, char, or varchar data types must be specified in number of characters. Offsets that use the text, image, binary, or varbinary data types must be specified in number of bytes.
Compatibility levels can affect return values. For more information about compatibility levels, see sp_dbcmptlevel (Transact-SQL).
A. Using SUBSTRING with a character string
The following example shows how to return only a part of a character string. From the
Contact table, this query returns the last name in one column with only the first initial in the second column.
USE AdventureWorks; GO SELECT LastName, SUBSTRING(FirstName, 1, 1) AS Initial FROM Person.Contact WHERE LastName like 'Barl%' ORDER BY LastName
Here is the result set.
LastName Initial --------------------------------- ------- Barley R Barlow B (2 row(s) affected)
Here is how to display the second, third, and fourth characters of the string constant
SELECT x = SUBSTRING('abcdef', 2, 3)
Here is the result set.
x ---------- bcd (1 row(s) affected)
B. Using SUBSTRING with text, ntext, and image data
To run the following examples, you must install the pubs database. For information about how to install the pubs database, see Downloading Northwind and pubs Sample Databases.
The following example shows how to return the first 200 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 SELECT pub_id, SUBSTRING(logo, 1, 10) AS logo, SUBSTRING(pr_info, 1, 10) AS pr_info FROM pub_info WHERE pub_id = '1756'
Here is the result set.
pub_id logo pr_info ------ ---------------------- ---------- 1756 0x474946383961E3002500 This is sa (1 row(s) affected)
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 FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'npub_info') DROP TABLE npub_info GO -- Create npub_info table in pubs database. Borrowed from instpubs.sql. USE pubs GO CREATE TABLE npub_info ( pub_id char(4) NOT NULL REFERENCES 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') INSERT npub_info values('0877', N'üThis is sample text data for Binnet & Hardley, publisher 0877 in the pubs databa') INSERT npub_info values('1389', N'üThis is sample text data for Algodata Infosystems, publisher 1389 in the pubs da') INSERT npub_info values('9952', N'üThis is sample text data for Scootney Books, publisher 9952 in the pubs database') INSERT npub_info values('1622', N'üThis is sample text data for Five Lakes Publishing, publisher 1622 in the pubs d') INSERT npub_info values('1756', N'üThis is sample text data for Ramona Publishers, publisher 1756 in the pubs datab') INSERT npub_info values('9901', N'üThis is sample text data for GGG&G, publisher 9901 in the pubs database. GGG&G i') INSERT npub_info values('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. SELECT pr.pub_id, SUBSTRING(pr.pr_info, 1, 35) AS pr_info, SUBSTRING(npr.pr_info, 1, 35) AS npr_info FROM pub_info pr INNER JOIN npub_info npr ON pr.pub_id = npr.pub_id ORDER BY pr.pub_id ASC