• Article

CHARINDEX (Transact-SQL)

Searches expression2 for expression1 and returns its starting position if found. The search starts at start_location.

Topic link iconTransact-SQL Syntax Conventions

Syntax

CHARINDEX (expression1 ,expression2 [ ,start_location ] )

Arguments

  • expression1
    Is a character expression that contains the sequence to be found. expression1 is limited to 8000 characters.

  • expression2
    Is a character expression to be searched.

  • start_location
    Is an integer or bigint expression at which the search starts. If start_location is not specified, is a negative number, or is 0, the search starts at the beginning of expression2.

Return Types

bigint if expression2 is of the varchar(max), nvarchar(max) or varbinary(max) data types; otherwise, int.

Remarks

If either expression1 or expression2 is of a Unicode data type (nvarchar or nchar) and the other is not, the other is converted to a Unicode data type. CHARINDEX cannot be used with the image data type.

If either expression1 or expression2 is NULL, CHARINDEX returns NULL when the database compatibility level is 70 or higher. If the database compatibility level is 65 or lower, CHARINDEX returns NULL only when both expression1 and expression2 are NULL.

If expression1 is not found within expression2, CHARINDEX returns 0.

CHARINDEX performs comparisons based on the collation of the input. To perform a comparison in a specified collation, you can 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.

Examples

The following example returns the position at which the sequence of characters bicycle starts in the DocumentSummary column of the Document table.

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

The following example uses the optional start_location parameter to start looking for vital in the fifth character of the DocumentSummary column.

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)

The following example shows the result set when expression1 is not found within expression2.

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)

The following example performs a case sensitive search for the string 'TEST' in 'Das jst ein Test'.

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

Here is the result set.

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

The following example performs a case sensitive search for the string 'Test' in 'Das jst ein Test'.

USE tempdb;
GO
SELECT CHARINDEX ( 'Test',
       'Das ist ein Test'
       COLLATE Latin1_General_CS_AS)

Here is the result set.

----------- 
13

The following example performs a case insensitive search for the string 'TEST' in 'Das jst ein Test'.

USE tempdb;
GO
SELECT CHARINDEX ( 'Test',
       'Das ist ein Test'
       COLLATE Latin1_General_CI_AS)
GO

Here is the result set.

----------- 
13