In SQL Server, you can use the T-SQL CHARINDEX() function or the PATINDEX() function to find a string within another string. Here’s a quick overview of each function.
The CHARINDEX() Function
This function accepts 3 arguments; the string to find, the string to search, and an optional start position.
The CHARINDEX() 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.');
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('Kate', 'Bob likes beer. Bob also likes beef.');
Result:
0
Specifying a Starting Position
You can specify a starting position for where to start searching. 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 string (not from the start position).
If this sounds confusing, the following example should help:
SELECT CHARINDEX('Bob', 'Bob likes beer. Bob also likes beef.', 16);
Result:
17
So 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:
Case-Sensitive
Here’s a case-sensitive search:
SELECT CHARINDEX('Beer', 'Bob likes beer.' COLLATE Latin1_General_CS_AS);
Result:
0
This is case-sensitive because _CS stands for Case-Sensitive.
Case-Insensitive
And here’s a case-insensitive search:
SELECT CHARINDEX('Beer', 'Bob likes beer.' COLLATE Latin1_General_CI_AS);
Result:
11
This is case-insensitive because _CI stands for Case-Insensitive.
The PATINDEX() Function
The PATINDEX() function does a similar job to CHARINDEX(). You basically have a choice of which one to use. The main difference is in the syntax.
The PATINDEX() function’s syntax goes like this:
PATINDEX ( '%pattern%' , expression )
Where pattern is a character expression that contains the sequence to be found, and expression is the expression to be searched (typically a column).
The PATINDEX() accepts wildcard characters, but not a starting position. CHARINDEX() on the other hand accepts a starting position, but not wildcard characters.
Examples
Here’s an example:
SELECT PATINDEX('%eer%', 'Bob likes beer.');
Result:
12
However, here’s what happens when we don’t include the wildcard characters:
SELECT PATINDEX('eer', 'Bob likes beer.');
Result:
0
Here’s another example where we introduce another wildcard character:
SELECT PATINDEX('%b_er%', 'Bob likes beer.');
Result:
11
In this case, the underscore (_) which is a wildcard for any single character.