SUBSTRING (Transact-SQL)SUBSTRING (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 是Azure Synapse Analytics (SQL DW) 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

SQL ServerSQL Server 中傳回字元、二進位、文字或影像運算式的一部分。Returns part of a character, binary, text, or image expression in SQL ServerSQL Server.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

SUBSTRING ( expression ,start , length )  

引數Arguments

expressionexpression
這是 characterbinarytextntextimage expressionIs a character, binary, text, ntext, or image expression.

startstart
這是指定傳回之字元開始的整數或 bigint 運算式。Is an integer or bigint expression that specifies where the returned characters start. (編號是以 1 為基礎,這表示運算式中的第一個字元為 1)。(The numbering is 1 based, meaning that the first character in the expression is 1). 如果 start 小於 1,傳回的運算式將會從 expression 內指定的第一個字元開始。If start is less than 1, the returned expression will begin at the first character that is specified in expression. 在此情況下,傳回的字元數會是 start + length 的總和 -1 或是 0 (以最大值為準)。In this case, the number of characters that are returned is the largest value of either the sum of start + length- 1 or 0. 如果 start 大於值運算式中的字元數,則會傳回長度為零的運算式。If start is greater than the number of characters in the value expression, a zero-length expression is returned.

lengthlength
這是一個正整數,或是指定將傳回之 expression 字元數的 bigint 運算式。Is a positive integer or bigint expression that specifies how many characters of the expression will be returned. 如果 length 是負數,則會產生錯誤並結束此陳述式。If length is negative, an error is generated and the statement is terminated. 如果 startlength 的總和大於 expression 中的字元數,則會傳回從 start 開始的整個值運算式。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.

傳回類型Return Types

如果 expression 是其中一個支援的字元資料類型,就會傳回字元資料。Returns character data if expression is one of the supported character data types. 如果 expression 是支援的 binary資料類型之一,就會傳回二進位資料。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/textchar/varchar/text varcharvarchar
nchar/nvarchar/ntextnchar/nvarchar/ntext nvarcharnvarchar
binary/varbinary/imagebinary/varbinary/image varbinaryvarbinary

RemarksRemarks

startlength 的值必須指定為字元數 (適用於 ntext charvarchar 資料類型) 和位元組數 (適用於 textimagebinaryvarbinary 資料類型)。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.

startlength 包含大於 2147483647 的值時,expression 必須是 varchar(max)varbinary(max)The expression must be varchar(max) or varbinary(max) when the start or length contains a value larger than 2147483647.

補充字元 (Surrogate 字組)Supplementary Characters (Surrogate Pairs)

當使用增補字元 (SC) 定序時,startlength 會將 expression 中的每個代理字組算成單一字元。When using supplementary character (SC) collations, both start and length count each surrogate pair in expression as a single character. 如需詳細資訊,請參閱 Collation and Unicode SupportFor more information, see Collation and Unicode Support.

範例Examples

A.A. 使用 SUBSTRING 與字元字串Using SUBSTRING with a character string

下列範例會顯示如何只傳回字元字串的一部分。The following example shows how to return only a part of a character string. sys.databases 資料表,此查詢會傳回第一個資料行中的系統資料庫名稱、第二個資料行中的資料庫第一個字母,以及最後一個資料行中的第三和第四個字元。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
FROM sys.databases  
WHERE database_id < 5;   

以下為結果集:Here is the result set.

NAMEname InitialInitial ThirdAndFourthCharactersThirdAndFourthCharacters
mastermaster mm stst
tempdbtempdb tt mpmp
modelmodel mm dede
msdbmsdb mm dbdb

以下是如何顯示字串常數 abcdef 的第二、第三和第四個字元。Here is how to display the second, third, and fourth characters of the string constant abcdef.

SELECT x = SUBSTRING('abcdef', 2, 3);  

以下為結果集:Here is the result set.

x  
----------  
bcd  
 
(1 row(s) affected)

B.B. 使用 SUBSTRING 與 text、ntext 以及 image 資料Using SUBSTRING with text, ntext, and image data

注意

若要執行下列範例,您必須安裝 pubs 資料庫。To run the following examples, you must install the pubs database.

下列範例示範如何從 pubs 資料庫之 pub_info資料表的各個 textimage資料行傳回前 10 個字元。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 資料會當成 varchar 傳回,且 image 資料會當成 varbinary 傳回。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)

下列範例會顯示 textntext 資料的 SUBSTRING 效果。The following example shows the effect of SUBSTRING on both text and ntext data. 首先,這個範例會在名稱為 pubsnpub_info 資料庫中,建立一份新的資料表。First, this example creates a new table in the pubs database named npub_info. 其次,這個範例會從 pr_info 資料行的前 80 個字元中,建立 npub_info 資料表的 pub_info.pr_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. 最後,INNER JOIN 會擷取所有簽發者識別碼,以及 textntext 發行者資訊資料行的 SUBSTRINGLastly, 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')  
,('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.  
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;  

範例:Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW)平行處理資料倉儲Parallel Data WarehouseExamples: Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) and 平行處理資料倉儲Parallel Data Warehouse

C.C. 使用 SUBSTRING 與字元字串Using SUBSTRING with a character string

下列範例會顯示如何只傳回字元字串的一部分。The following example shows how to return only a part of a character string. 這個查詢會從 dbo.DimEmployee 資料表中,傳回第一個資料行中的姓氏,而第二資料行只有第一個首字母。From the dbo.DimEmployee table, this query returns the last name in one column with only the first initial in the second column.

-- Uses AdventureWorks  
  
SELECT LastName, SUBSTRING(FirstName, 1, 1) AS Initial  
FROM dbo.DimEmployee  
WHERE LastName LIKE 'Bar%'  
ORDER BY LastName;  

以下為結果集:Here is the result set.

LastName             Initial
-------------------- -------
Barbariol            A
Barber               D
Barreto de Mattos    P

下列範例會顯示如何傳回字串常數 abcdef 的第二、第三和第四個字元。The following example shows how to return the second, third, and fourth characters of the string constant abcdef.

USE ssawPDW;  
  
SELECT TOP 1 SUBSTRING('abcdef', 2, 3) AS x FROM dbo.DimCustomer;  

以下為結果集:Here is the result set.

x
-----
bcd

另請參閱See Also

LEFT (Transact-SQL)LEFT (Transact-SQL)
LTRIM (Transact-SQL)LTRIM (Transact-SQL)
RIGHT (Transact-SQL)RIGHT (Transact-SQL)
RTRIM (Transact-SQL)RTRIM (Transact-SQL)
STRING_SPLIT (Transact-SQL)STRING_SPLIT (Transact-SQL)
TRIM (Transact-SQL)TRIM (Transact-SQL)
字串函數 (Transact-SQL)String Functions (Transact-SQL)