CHARINDEX (Transact-SQL)CHARINDEX (Transact-SQL)

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

此函式會在第二個字元運算式內搜尋一個字元運算式,並傳回第一個運算式的開始位置 (如果找到的話)。This function searches for one character expression inside a second character expression, returning the starting position of the first expression if found.

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

語法Syntax

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )   

引數Arguments

expressionToFindexpressionToFind
包含要尋找之順序的字元 expressionA character expression containing the sequence to find. expressionToFind 具有 8000 字元限制。expressionToFind has an 8000 character limit.

expressionToSearchexpressionToSearch
要搜尋的字元運算式。A character expression to search.

start_locationstart_location
搜尋開始的 integerbigint 運算式。An integer or bigint expression at which the search starts. 如果未指定 start_location,或者它是負數或零 (0) 值,則搜尋會從 expressionToSearch 開頭開始。If start_location is not specified, has a negative value, or has a zero (0) value, the search starts at the beginning of expressionToSearch.

傳回類型Return types

如果 expressionToSearch 具有 nvarchar(max)varbinary(max)varchar(max) 資料類型,則為 bigint;否則為 intbigint if expressionToSearch has an nvarchar(max), varbinary(max), or varchar(max) data type; int otherwise.

RemarksRemarks

如果 expressionToFindexpressionToSearch 運算式具有 Unicode 資料類型 (ncharnvarchar),但另一個運算式沒有,則 CHARINDEX 函式會將這個其他運算式轉換成 Unicode 資料類型。If either the expressionToFind or expressionToSearch expression has a Unicode data type (nchar or nvarchar), and the other expression does not, the CHARINDEX function converts that other expression to a Unicode data type. CHARINDEX 不得與 imagentexttext 資料類型搭配使用。CHARINDEX cannot be used with image, ntext, or text data types.

如果 expressionToFindexpressionToSearch 運算式具有 NULL 值,則 CHARINDEX 會傳回 NULL。If either the expressionToFind or expressionToSearch expression has a NULL value, CHARINDEX returns NULL.

如果在 expressionToSearch 內找不到 expressionToFind,則 CHARINDEX 會傳回 0。If CHARINDEX does not find expressionToFind within expressionToSearch, CHARINDEX returns 0.

CHARINDEX 會根據輸入定序來執行比較。CHARINDEX performs comparisons based on the input collation. 若要執行指定定序的比較,請使用 COLLATE,將明確定序套用至輸入。To perform a comparison in a specified collation, use COLLATE to apply an explicit collation to the input.

傳回的開始位置是以 1 為基準,而不是以 0 為基準。The starting position returned is 1-based, not 0-based.

0x0000 (char(0) ) 是 Windows 定序中未定義的字元,而且不得包含在 CHARINDEX 中。0x0000 (char(0)) is an undefined character in Windows collations and cannot be included in CHARINDEX.

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

使用 SC 定序時,start_location 和傳回值會將代理字組計算成一個字元,而不是兩個字元。When using SC collations, both start_location and the return value count surrogate pairs as one character, not two. 如需詳細資訊,請參閱 Collation and Unicode SupportFor more information, see Collation and Unicode Support.

範例Examples

A.A. 傳回運算式的開始位置Returning the starting position of an expression

此範例會在已搜尋字串值變數 @document 中搜尋 bicycleThis example searches for bicycle in the searched string value variable @document.

DECLARE @document varchar(64);  
SELECT @document = 'Reflectors are vital safety' +  
                   ' components of your bicycle.';  
SELECT CHARINDEX('bicycle', @document);  
GO  

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

-----------   
48            

B.B. 從特定位置執行搜尋Searching from a specific position

此範例會使用選擇性 start_location 參數,在已搜尋字串值變數 @document 的第五個字元開始搜尋 vitalThis example uses the optional start_location parameter to start the search for vital at the fifth character of the searched string value variable @document.

DECLARE @document varchar(64);  
  
SELECT @document = 'Reflectors are vital safety' +  
                   ' components of your bicycle.';  
SELECT CHARINDEX('vital', @document, 5);  
GO  

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

-----------   
16            
  
(1 row(s) affected)  

C.C. 搜尋不存在的運算式Searching for a nonexistent expression

此範例會顯示 CHARINDEX 在 expressionToSearch 內找不到 expressionToFind 時的結果集。This example shows the result set when CHARINDEX does not find expressionToFind within expressionToSearch.

DECLARE @document varchar(64);  
  
SELECT @document = 'Reflectors are vital safety' +  
                   ' components of your bicycle.';  
SELECT CHARINDEX('bike', @document);  
GO  

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

-----------
0
  
(1 row(s) affected)

此範例會顯示已搜尋字串 'This is a Test``' 中字串 'TEST' 的區分大小寫搜尋。This example shows a case-sensitive search for the string 'TEST' in searched string 'This is a Test``'.

USE tempdb;  
GO  
--perform a case sensitive search  
SELECT CHARINDEX ( 'TEST',  
       'This is a Test'  
       COLLATE Latin1_General_CS_AS);  

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

-----------
0

此範例會顯示 'This is a Test' 中字串 'Test' 的區分大小寫搜尋。This example shows a case-sensitive search for the string 'Test' in 'This is a Test'.

  
USE tempdb;  
GO  
SELECT CHARINDEX ( 'Test',  
       'This is a Test'  
       COLLATE Latin1_General_CS_AS);  

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

-----------
11

此範例會顯示 'This is a Test' 中字串 'TEST' 的不區分大小寫搜尋。This example shows a case-insensitive search for the string 'TEST' in 'This is a Test'.

  
USE tempdb;  
GO  
SELECT CHARINDEX ( 'TEST',  
       'This is a Test'  
       COLLATE Latin1_General_CI_AS);  
GO  

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

-----------
11

範例:Azure SQL 資料倉儲Azure SQL Data Warehouse平行處理資料倉儲Parallel Data WarehouseExamples: Azure SQL 資料倉儲Azure SQL Data Warehouse and 平行處理資料倉儲Parallel Data Warehouse

F.F. 從字串運算式的開頭搜尋Searching from the start of a string expression

此範例會傳回 This is a string 字串中 is 字串的第一個位置,並且從 This is a string 的位置 1 (第一個字元) 開始進行。This example returns the first location of the string is in string This is a string, starting from position 1 (the first character) of This is a string.

SELECT CHARINDEX('is', 'This is a string');  

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

---------
3

G.G. 從第一個位置以外的位置執行搜尋Searching from a position other than the first position

此範例會傳回 This is a string 字串中 is 字串的第一個位置,並從位置 4 開始搜尋。This example returns the first location of the string is in string This is a string, starting the search from position 4 (the fourth character).

SELECT CHARINDEX('is', 'This is a string', 4);  

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

---------
 6

H.H. 找不到字串時的結果Results when the string is not found

此範例會顯示 CHARINDEX 在已搜尋字串中找不到 string_pattern 字串時的傳回值。This example shows the return value when CHARINDEX does not find string string_pattern in the searched string.

SELECT TOP(1) CHARINDEX('at', 'This is a string') FROM dbo.DimCustomer;  

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

---------
0

另請參閱See also

LEN (Transact-SQL)LEN (Transact-SQL)
PATINDEX (Transact-SQL)PATINDEX (Transact-SQL)
字串函數 (Transact-SQL)String Functions (Transact-SQL)
+ (字串串連) (Transact-SQL)+ (String Concatenation) (Transact-SQL)
定序與 Unicode 支援Collation and Unicode Support