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

Note that the CHARINDEX function does not support one of the syntax variations that POSITION supports.

Syntax

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

Arguments

Required:

expr1

A string or binary expression representing the value to look for.

expr2

A string or binary expression representing the value to search.

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, the function returns NULL.

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

  • If the specified optional start_pos is beyond the end of the second argument (the string to search), the function returns 0.

  • If the first argument is empty (e.g. an empty string), the function 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 support the following collation specifications:

  • pi (punctuation-insensitive).

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

Note

To use this function with a column that has the upper or lower collation specifiers, you must enable the 2024_02 behavior change bundle in your account.

To enable this bundle in your account, execute the following statement:

SELECT SYSTEM$ENABLE_BEHAVIOR_CHANGE_BUNDLE('2024_02');
Copy

Examples

VARCHAR Expressions

Find the first occurrence of ‘an’ in ‘banana’:

select charindex('an', 'banana', 1);
+------------------------------+
| CHARINDEX('AN', 'BANANA', 1) |
|------------------------------|
|                            2 |
+------------------------------+
Copy

Find the first occurrence of ‘an’ in ‘banana’ at or after position 3. This search finds the second occurrence of ‘an’.

select charindex('an', 'banana', 3);
+------------------------------+
| CHARINDEX('AN', 'BANANA', 3) |
|------------------------------|
|                            4 |
+------------------------------+
Copy

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

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

+--------+---------------------+-----------------+
| N      | H                   | CHARINDEX(N, 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 |
+--------+---------------------+-----------------+
Copy

BINARY Expressions

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', X'ABCDEF') |
|-----------------------------|
|                           3 |
+-----------------------------+
Copy

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', X'ABCD') |
|---------------------------|
|                         0 |
+---------------------------+
Copy