If you’ve ever needed to search for a string within another string in SQL Server, the CHARINDEX function is your go-to. It’s quite similar to the INSTR function found in other databases.

Let me walk you through how it works, and I’ll show you a few examples to make things even clearer.

What is the SQL CHARINDEX Function?

The CHARINDEX function helps me find a specific string within another string, starting from a location I choose. It tells me the position of the first occurrence of the searched string or returns 0 if it’s not found.

This function behaves a lot like INSTR, but only the first match is returned.

Here’s the syntax:

CHARINDEX ( stringToFind, stringToSearchIn [, startLocation] )
  • stringToFind: The smaller string I’m searching for.
  • stringToSearchIn: The larger string that contains the smaller one.
  • startLocation (optional): Where I want the search to begin. Remember, the first character is at position 1.

Simple CHARINDEX Example

Let me show you a basic use case for the CHARINDEX function. Say I want to find the word “dogs” within a sentence:

SELECT
  CHARINDEX('dogs', 'The family has two cute dogs named Buddy and Max.');

Result:

24

The word “dogs” starts at position 24 in the string.

CHARINDEX for a Single Character

I can even search for individual characters using CHARINDEX.

SELECT
  CHARINDEX('a', 'The family has two cute dogs named Buddy and Max.');

Result:

9

The result is 9 because the first “a” appears in the word “has.” The function stops at the first match and ignores all later ones.

What Happens When There’s No Match?

Here’s what I see when the search string isn’t found:

SELECT
  CHARINDEX('cat', 'The family has two cute dogs named Buddy and Max.');

Result:

0

This happens because the word “cat” isn’t in the original string.

Starting the Search from a Specific Position

Sometimes, I need to find a second or subsequent occurrence of a word. For example, the first appearance of “dogs” is at position 24. Now, if I want to find the next occurrence, I can start my search at position 25 or later:

SELECT
  CHARINDEX('dogs', 'The family has two cute dogs. These dogs love to play.', 25);

Result:

39

That tells me the next occurrence starts at position 39.

Case Sensitivity with CHARINDEX

By default, CHARINDEX performs a case-insensitive search. This means it doesn’t matter if the string has uppercase or lowercase letters; the result will be the same.

Here’s an example:

SELECT
  CHARINDEX('t', 'The family has two cute dogs.');

Result:

1

The first “t” is at the very start.

But what if I want the search to be case-sensitive? I can make it case-sensitive by adding the COLLATE clause:

SELECT
  CHARINDEX('T', 'The family has two cute dogs.' COLLATE Latin1_General_CS_AS);

Result:

0

The result is 0 because there’s no uppercase “T” in the search string (according to this collation setting).

If I search for a lowercase “t” instead:

SELECT
  CHARINDEX('t', 'The family has two cute dogs.' COLLATE Latin1_General_CS_AS);

Result:

1

The “CS” in Latin1_General_CS_AS stands for Case Sensitive, ensuring my search respects letter casing.

Conclusion

Using the CHARINDEX function, I can efficiently search for a string within another string in SQL Server. It behaves similarly to the INSTR function found in other databases, making it familiar if you’ve used that before. Whether I need a basic search or a case-sensitive one, CHARINDEX handles it with ease.

So, the next time you need to find a substring, just remember how powerful and flexible CHARINDEX can be!

RELATED ARTICLESMORE FROM AUTHOR