CHARINDEX (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
This function searches for one character expression inside a second character expression, returning the starting position of the first expression if found.
Transact-SQL syntax conventions
Syntax
CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Arguments
expressionToFind
A character expression containing the sequence to find. expressionToFind has an 8000 character limit.
expressionToSearch
A character expression to search.
start_location
An integer or bigint expression at which the search starts. 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
bigint if expressionToSearch has an nvarchar(max), varbinary(max), or varchar(max) data type; int otherwise.
Remarks
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 cannot be used with image, ntext, or text data types.
If either the expressionToFind or expressionToSearch expression has a NULL value, CHARINDEX returns NULL.
If CHARINDEX does not find expressionToFind within expressionToSearch, CHARINDEX returns 0.
CHARINDEX performs comparisons based on the input collation. To perform a comparison in a specified collation, use COLLATE to apply an explicit collation to the input.
The starting position returned is 1-based, not 0-based.
0x0000 (char(0)) is an undefined character in Windows collations and cannot be included in CHARINDEX.
Supplementary Characters (Surrogate Pairs)
When using SC collations, both start_location and the return value count surrogate pairs as one character, not two. For more information, see Collation and Unicode Support.
Examples
A. Returning the starting position of an expression
This 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. Searching from a specific position
This 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. Searching for a nonexistent expression
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)
D. Performing a case-sensitive search
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 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
E. Performing a case-insensitive search
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
Examples: Azure Synapse Analytics and Analytics Platform System (PDW)
F. Searching from the start of a string expression
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. Searching from a position other than the first position
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. Results when the string is not found
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)
PATINDEX (Transact-SQL)
String Functions (Transact-SQL)
+ (String Concatenation) (Transact-SQL)
Collation and Unicode Support