In SQL Server, you can use the T-SQL CHARINDEX() function to find the starting position of a character expression within another character expression.

You provide both character expressions as arguments. You can also provide an optional argument to specify a position in which to start the search.

Syntax

The syntax goes like this:

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )

Where expressionToFind is the expression you want to find in the other string, and expressionToSearch is the other string. The optional start_location can be used to specify a position within expressionToSearch for which to start searching.

Note that only the position of the first occurrence is returned.

Example

Here’s an example:

SELECT CHARINDEX('Bob', 'Bob likes beer. Bob also likes beef.') AS Result;

Result:

+----------+
| Result   |
|----------|
| 1        |
+----------+

In this example, the first argument is Bob, which means that we’re searching the second argument for Bob. The result is 1 because that’s the position where Bob first appears in the second argument.

You might also notice that Bob actually appears twice in the string, but only the position of the first match is returned.

No Match

If the second argument didn’t contain Bob the result would’ve been 0.

SELECT CHARINDEX('Bob', 'Kate likes beer. Kate also likes beef.') AS Result;

Result:

+----------+
| Result   |
|----------|
| 0        |
+----------+

Specifying a Starting Position

You can (optionally) specify a starting position for the search. This means that SQL Server will skip any occurrence that comes before that starting position. However, the results are still reported based on its position within the whole string (not from your chosen starting position).

Here’s an example to demonstrate:

SELECT CHARINDEX('Bob', 'Bob likes beer. Bob also likes beef.', 16) AS Result;

Result:

+----------+
| Result   |
|----------|
| 17       |
+----------+

In this example we start searching at position 16 (which happens to be the space before the 2nd Bob). The result is that the first occurrence of Bob is skipped, and the second one’s position is returned. And we can see that its position is 17 characters from the start of the string (even though it’s only one character from where we started searching).

Case-Sensitivity

You can explicitly perform a case-sensitive search by adding the COLLATE clause to your SELECT statement:

Here’s an example that includes a case-sensitive search and a case-insensitive search:

SELECT 
  CHARINDEX('Beer', 'Bob likes beer.' COLLATE Latin1_General_CS_AS) AS 'Case-Sensitive',
  CHARINDEX('Beer', 'Bob likes beer.' COLLATE Latin1_General_CI_AS) AS 'Case-Insensitive';

Result:

+------------------+--------------------+
| Case-Sensitive   | Case-Insensitive   |
|------------------+--------------------|
| 0                | 11                 |
+------------------+--------------------+

The first one is case-sensitive because _CS (in the collation) stands for Case-Sensitive. The second one is case-insensitive because _CI stands for Case-Insensitive.