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

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel 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 ] )   

备注

若要查看 SQL Server 2014 及更早版本的 Transact-SQL 语法,请参阅早期版本文档To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

参数Arguments

expressionToFind expressionToFind
一个字符表达式,其中包含要查找的序列。A character expression containing the sequence to find. expressionToFind 限制为 8000 个字符 。expressionToFind has an 8000 character limit.

expressionToSearch expressionToSearch
要搜索的字符表达式。A character expression to search.

start_location start_location
表示搜索开始位置的 integer 或 bigint 表达式 。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;否则为 int 。bigint if expressionToSearch has an nvarchar(max), varbinary(max), or varchar(max) data type; int otherwise.

备注Remarks

如果 expressionToFind 或 expressionToSearch 表达式具有一个 Unicode 数据类型(nchar 或 nvarchar),而其他的表达式不具有,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 不能与 image、ntext 和 text 数据类型一起使用 。CHARINDEX cannot be used with image, ntext, or text data types.

如果 expressionToFind 或 expressionToSearch 表达式具有 NULL 值,CHARINDEX 则返回 NULL 。If either the expressionToFind or expressionToSearch expression has a NULL value, CHARINDEX returns NULL.

如果 CHARINDEX 在 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.

补充字符(代理项对)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. 有关详细信息,请参阅 排序规则和 Unicode 支持For 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 Synapse AnalyticsAzure Synapse Analytics并行数据仓库Parallel Data WarehouseExamples: Azure Synapse AnalyticsAzure Synapse Analytics 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