CHARINDEX (Transact-SQL)

Searches an expression for another expression and returns its starting position if found.

Topic link icon Transact-SQL Syntax Conventions

Syntax

CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] )

Arguments

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

  • expressionToSearch
    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 expressionToSearch.

Return Types

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

Remarks

If either expressionToFind or expressionToSearch 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 text, ntext, and image data types.

If either expressionToFind or expressionToSearch is NULL, CHARINDEX returns NULL.

If expressionToFind is not found within expressionToSearch, 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.

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

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

USE AdventureWorks2012;
GO
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

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

USE AdventureWorks2012;
GO
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

The following example shows the result set when expressionToFind is not found within expressionToSearch.

USE AdventureWorks2012;
GO
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 ist 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 ist 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 ist ein Test'.

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

Here is the result set.

-----------

13

See Also

Reference

String Functions (Transact-SQL)

+ (String Concatenation) (Transact-SQL)

Concepts

Collation and Unicode Support