CHARINDEX (Transact-SQL)

Returns the starting position of the specified expression in a character string.

Topic link iconTransact-SQL Syntax Conventions

Syntax

CHARINDEX ( expression1 ,expression2 [ , start_location ] ) 

Arguments

  • expression1
    Is an expression that contains the sequence of characters to be found. expression1 is an expression of the character string data type category.
  • expression2
    Is an expression, typically a column searched for the specified sequence. expression2is of the character string data type category.
  • start_location
    Is the character position to start searching for expression1 in expression2. If start_location is not specified, is a negative number, or is zero, the search starts at the beginning of expression2. start_location can be of type bigint.

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 text, ntext, and image data types.

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.

Examples

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

USE AdventureWorks;
GO
SELECT CHARINDEX('bicycle', DocumentSummary)
FROM Production.Document
WHERE DocumentID = 3;
GO

Here is the result set.

----------- 
48          

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

USE AdventureWorks;
GO
SELECT CHARINDEX('bicycle', DocumentSummary, 5)
FROM Production.Document
WHERE DocumentID = 3;
GO

Here is the result set.

----------- 
48          

(1 row(s) affected)

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

USE AdventureWorks;
GO
SELECT CHARINDEX('bike', DocumentSummary)
FROM Production.Document
WHERE DocumentID =6;
GO

Here is the result set.

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

(1 row(s) affected)

The following example uses the COLLATE function.

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

See Also

Reference

+ (String Concatenation) (Transact-SQL)
String Functions (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance