CHARINDEX

CHARINDEX is a 1-based indexing function (both input and output) that returns the starting position of a specified substring within a given string.

See also PATINDEX, REGEXP_INSTR.

Syntax

CHARINDEX ( needle_string_expr , haystack_string_expr [ , start_location ] )

Parameters

Parameter

Description

needle_string_expr

String to find

haystack_string_expr

String to search within

start_location

An integer at which the search starts. This value is optional and when not supplied, the search starts at the beggining of needle_string_expr

Returns

Integer start position of a match, or 0 if no match was found.

If one of the parameters is NULL, then the return value is NULL.

Empty string search returns 0.

Examples

For these examples, consider the following table:

id  | username    | email                   |
----+-------------+-------------------------+
1   | john_doe    | [email protected]    |
----+-------------+-------------------------+
2   | jane_doe    | [email protected]    |
----+-------------+-------------------------+
3   | bob_smith   | [email protected]   |
----+-------------+-------------------------+
4   | susan_jones | [email protected] |
SELECT CHARINDEX('doe', username) FROM users;

Output:

charindex|
---------+
6        |
6        |
0        |
0        |
SELECT CHARINDEX('doe', username, 10) FROM users;

Output:

charindex|
---------+
0        |
0        |
0        |
0        |
SELECT CHARINDEX('jane_doe', username, -10) FROM users;
charindex|
---------+
0        |
1        |
0        |
0        |