Edytuj

Udostępnij za pośrednictwem

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 ] )

Arguments

expressionToFind

A character expression containing the sequence to find. expressionToFind has an 8,000 character limit.

expressionToSearch

A character expression to search.

start_location

An integer or bigint expression at which the search starts. If start_location isn't 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 doesn't, the CHARINDEX function converts that other expression to a Unicode data type. CHARINDEX can't 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 doesn't 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 can't be included in CHARINDEX.

Supplementary Characters (Surrogate Pairs)

When using supplementary character (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. Return the starting position of an expression

This example searches for bicycle in the searched string value variable @document.

DECLARE @document AS VARCHAR (64);

SELECT @document = 'Reflectors are vital safety' +
    ' components of your bicycle.';

SELECT CHARINDEX('bicycle', @document);
GO

Here's the result set.

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

B. Search 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 AS VARCHAR (64);

SELECT @document = 'Reflectors are vital safety' +
    ' components of your bicycle.';

SELECT CHARINDEX('vital', @document, 5);
GO

Here's the result set.

-----------
16

C. Search for a nonexistent expression

This example shows the result set when CHARINDEX doesn't find expressionToFind within expressionToSearch.

DECLARE @document AS VARCHAR (64);

SELECT @document = 'Reflectors are vital safety' +
    ' components of your bicycle.';

SELECT CHARINDEX('bike', @document);
GO

Here's the result set.

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

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's the result set.

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

This example shows a case-sensitive search for the string Test in searched string This is a Test.

USE tempdb;
GO

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

Here's the result set.

-----------
11

This example shows a case-insensitive search for the string TEST in searched string This is a Test.

USE tempdb;
GO

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

Here's the result set.

-----------
11

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

F. Search 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's the result set.

---------
3

G. Search 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's the result set.

---------
 6

H. Results when the string isn't found

This example shows the return value when CHARINDEX doesn't find string string_pattern in the searched string.

SELECT TOP (1) CHARINDEX('at', 'This is a string')
FROM dbo.DimCustomer;

Here's the result set.

---------
0

Related content