Export (0) Print
Expand All

CHARINDEX (Transact-SQL)

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

Topic link icon Transact-SQL Syntax Conventions

CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] )

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.

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

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.

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 in the AdventureWorks2012 database.

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 in the AdventureWorks2012 database.

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.

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

The following example performs a case-sensitive search for the string 'TEST' in '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

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

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

Here is the result set.

-----------

13

E. Performing a case-insensitive search

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

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

Here is the result set.

-----------

13

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft