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 |
|---|---|
|
String to find |
|
String to search within |
|
An integer at which the search starts. This value is optional and when not supplied, the search starts at the beggining of |
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 | password
----+-------------+-------------------------+-----------
1 | john_doe | [email protected] | password1
----+-------------+-------------------------+-----------
2 | jane_doe | [email protected] | password2
----+-------------+-------------------------+-----------
3 | bob_smith | [email protected] | password3
----+-------------+-------------------------+-----------
4 | susan_jones | [email protected] | password4
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 |