SQL Server CHARINDEX Function Guide with Examples

Are you looking to find a string within another string in SQL Server? You can do that with the CHARINDEX function, which is SQL Server’s version of an INSTR function.

Learn how to use the function and see some examples in this article.

What is the SQL CHARINDEX Function?

The CHARINDEX function in SQL Server lets you search for one string inside another string, from a specific location. It returns the position of the string if it is found, or 0 if it is not found.

It’s comparable to INSTR in other databases and applications. Only the position of the first occurrence is shown.

The syntax of the CHARINDEX function looks like this:

CHARINDEX ( stringToFind, stringToSearch [, startLocation] )

• stringToFind is the string that you want to search for inside the other string. It’s almost always the smaller of the two.
• stringToSearchIn is the string that you want to search within. It’s the larger of the two.
• startLocation, an optional parameter, is the position in the stringToSearchIn that the search starts from. The start of the string is position 1.

Let’s take a look at some examples.

 

Simple CHARINDEX Example

The CHARINDEX function can be used to find a word within a larger string.

SELECT
CHARINDEX('bears', 'Once upon a time, there were three bears. These bears were called…');

Result:

36

The value of 36 is shown because the word “bear” is found at that position.

 

CHARINDEX for a Single Character

You can use CHARINDEX to look for a single character as well.

SELECT
CHARINDEX('e', 'Once upon a time, there were three bears. These bears were called…');

Result:

4

It shows the first location of the letter “e”, which is at the end of the word “Once”. All of the occurrences after that are ignored.

 

No Match

We can see what happens when the stringToFind is not found within the stringToSearchIn:

SELECT
CHARINDEX('zoo', 'Once upon a time, there were three bears. These bears were called…');

Result:

0

A value of 0 is shown, because the word “zoo” is not found in the second string parameter.

 

Specify Starting Position

We can use the third parameter to specify the starting position for searching. This means that the database will skip any occurrences before the specified character position.

For example, we can search for the second occurrence of the word “bears”. To do that, we know the first occurrence of “bears” is at position 36, as we saw in the example above. So, we can specify a starting position of any number greater than 36. Let’s try 38.

SELECT
CHARINDEX('bears', 'Once upon a time, there were three bears. These bears were called…', 38);

Result:

49

This means that position 49 is where another occurrence of “bears” is.

 

Case Sensitive Search

By default, the CHARINDEX function performs a case-insensitive search. This means it doesn’t care if the string you specify is in uppercase or lowercase.

For example, searching for “w” and “W” will show the same result:

SELECT
CHARINDEX('w', 'Once upon a time, there were three bears. These bears were called…');

Result:

25

What if you want to perform a case-sensitive search with CHARINDEX? You can do that by adding the COLLATE clause to the SELECT statement, inside the function:

SELECT
CHARINDEX('W', 'Once upon a time, there were three bears. These bears were called…' COLLATE Latin1_General_CS_AS);

Result:

0

This shows 0 because an uppercase “W” is not found. We can search for a lowercase “w” as well:

The “CS” inside “Latin1_General_CS_AS” stands for Case Sensitive.

SELECT
CHARINDEX('w', 'Once upon a time, there were three bears. These bears were called…' COLLATE Latin1_General_CS_AS);

Result:

25

 

Conclusion

To find a string within another string in SQL Server you can use the CHARINDEX function. It’s similar to the INSTR function in other databases. There are a few examples here of what happens if no match is found and how to perform a case-sensitive search.