Categories:

String & Binary Functions (Matching/Comparison)

CHARINDEX

Searches for the first occurrence of the first argument in the second argument and, if successful, returns the position (1-based) of the first argument in the second argument.

Aliases

POSITION

Syntax

CHARINDEX( <expr1>, <expr2> [ , <start_pos> ] )

Arguments

Required:

expr1

A string or binary expression representing the value we are looking for.

expr2

A string or binary expression representing the value in which we are searching.

Optional:

start_pos

A number indicating the position from where to start the search (with 1 representing the start of expr2).

Default: 1

Usage Notes

  • If any arguments are NULL, returns NULL.

  • If the string or binary value is not found, returns 0.

  • If the specified optional start_pos is beyond the end of the second argument (the string in which we are searching), returns 0.

  • If the first argument is empty (e.g. an empty string), returns 1.

  • The data types of the first two arguments should be the same; either both should be strings or both should be binary values.

Collation Details

This function does not work with the following collation specifications:

  • lower

  • upper

  • cs-ai (case-sensitive, accent-insensitive)

Examples

Find the first occurrence of ‘uzzy’ in ‘fuzzy wuzzy’:

SELECT CHARINDEX('uzzy', 'fuzzy wuzzy', 1);

+-------------------------------------+
| CHARINDEX('UZZY', 'FUZZY WUZZY', 1) |
|-------------------------------------|
|                                   2 |
+-------------------------------------+

Same example as before, but starting at position 5 so the second occurrence, rather than the first, is found:

SELECT CHARINDEX('uzzy', 'fuzzy wuzzy', 5);

+-------------------------------------+
| CHARINDEX('UZZY', 'FUZZY WUZZY', 5) |
|-------------------------------------|
|                                   8 |
+-------------------------------------+

Search for various characters, including unicode characters, in strings:

SELECT n, h, CHARINDEX(n, h) FROM pos;

+--------+---------------------+-------------------+
| N      | H                   | CHARINDEX(N IN H) |
|--------+---------------------+-------------------|
|        |                     |                 1 |
|        | sth                 |                 1 |
| 43     | 41424344            |                 5 |
| a      | NULL                |              NULL |
| dog    | catalog             |                 0 |
| log    | catalog             |                 5 |
| lésine | le péché, la lésine |                14 |
| nicht  | Ich weiß nicht      |                10 |
| sth    |                     |                 0 |
| ☃c     | ☃a☃b☃c☃d            |                 5 |
| ☃☃     | bunch of ☃☃☃☃       |                10 |
| ❄c     | ❄a☃c❄c☃             |                 5 |
| NULL   | a                   |              NULL |
| NULL   | NULL                |              NULL |
+--------+---------------------+-------------------+

Search for bytes in a binary value. Note that because the values below are hexadecimal representations, a single BINARY byte is represented as two hex digits.

In this example, the returned value is 3 because ‘EF’ matches the 3rd byte (the first byte is ‘AB’; the second byte is ‘CD’, and the third byte is ‘EF’):

SELECT CHARINDEX(X'EF', X'ABCDEF');

+-------------------------------+
| CHARINDEX(X'EF' IN X'ABCDEF') |
|-------------------------------|
|                             3 |
+-------------------------------+

In this example, there is no match. Although the sequence ‘BC’ appears to be in the value being searched, the ‘B’ is the second nybble of the first byte, and the ‘C’ is the first nybble of the second byte; no byte actually contains ‘BC’, so the returned value is 0 (not found).

SELECT CHARINDEX(X'BC', X'ABCD');

+-----------------------------+
| CHARINDEX(X'BC' IN X'ABCD') |
|-----------------------------+
|                           0 |
+-----------------------------+