DomainScoreGuthib

9/5/2025

SQL Server provides a built-in string function called CHARINDEX that simplifies the process of finding text within other text.

Mastering SQL CHARINDEX: How to Search for Text in Strings

When working with SQL Server, one of the most common tasks developers and database administrators face is searching for specific text within a string. This task, seemingly simple, can become quite intricate depending on the requirements of the query. Fortunately, SQL Server provides a built-in string function called CHARINDEX that simplifies the process of finding text within other text. Whether you’re debugging data, cleaning up records, or building complex logic, mastering CHARINDEX can significantly streamline your SQL queries.

What is CHARINDEX?

The CHARINDEX function in SQL Server is used to search for the position of a substring within a larger string. It returns the starting position of the substring if it’s found, and returns 0 if the substring is not present. This function is extremely useful for substring searches, conditional filters, and text manipulation.

The syntax for CHARINDEX is:

CHARINDEX ( expressionToFind , expressionToSearch [ , startLocation ] )
  • expressionToFind: The substring you are searching for.
  • expressionToSearch: The string within which you’re searching.
  • startLocation (optional): An integer that specifies where to start the search. If omitted, the search starts at the beginning of the string.

Basic Usage

Let’s look at a simple example to illustrate how CHARINDEX works.

SELECT CHARINDEX('cat', 'The black cat sat on the mat.') AS Position;

In this example, the function searches for the substring ‘cat’ in the sentence. It returns 11 because that is the position where ‘cat’ begins within the string.

Remember, CHARINDEX always returns a 1-based index, meaning it starts counting from 1, not 0.

Using CHARINDEX With the Start Location

The startLocation parameter is particularly useful when dealing with multiple occurrences of a substring. Suppose you want to find the position of the second occurrence of a word or character.

For example:

DECLARE @sentence NVARCHAR(100) = 'Check the door, then check the lock.';
DECLARE @firstOccurrence INT;

SET @firstOccurrence = CHARINDEX('check', @sentence);

SELECT CHARINDEX('check', @sentence, @firstOccurrence + 1) AS SecondOccurrence;

Here, we first find the position of the first occurrence of ‘check’, and then use that position to search again starting just after the first occurrence. The result gives us the position of the second match.

Case Sensitivity

One important point to note is that CHARINDEX is case-insensitive by default, depending on the collation settings of your database. This means that:

SELECT CHARINDEX('Cat', 'The cat slept.') AS Result;

Will return 0 in a case-sensitive collation, but 5 in a case-insensitive collation. You can force case sensitivity by using a binary collation like so:

SELECT CHARINDEX('Cat', 'The cat slept.' COLLATE Latin1_General_BIN) AS Result;

Always make it a habit to verify the collation settings when accurate matches are critical in your logic.

Practical Use Cases for CHARINDEX

CHARINDEX becomes extremely powerful in real-world scenarios. Below are several practical applications you might encounter as a SQL developer.

1. Extracting Text

You can use CHARINDEX in combination with other string functions like SUBSTRING to extract parts of a string. For example:

SELECT SUBSTRING('First:Second:Third', 1, CHARINDEX(':', 'First:Second:Third') - 1) AS FirstPart;

This will extract the word ‘First’ by finding the position of the first colon and using it as a boundary.

2. Input Validation

If you want to validate if a certain piece of text — like an “@” symbol in an email — is present, CHARINDEX does the trick:

SELECT CASE 
        WHEN CHARINDEX('@', '[email protected]') > 0 THEN 'Valid'
        ELSE 'Invalid'
    END AS EmailValidation;

3. Cleaning Data

You may also use CHARINDEX to identify incorrectly formatted data or apply logic to cleanse strings that follow varying formats.

Using CHARINDEX in WHERE Clauses

CHARINDEX is incredibly useful in filtering rows based on partial string matches in WHERE clauses:

SELECT * 
FROM Customers 
WHERE CHARINDEX('Inc', CompanyName) > 0;

This query will retrieve all records from the Customers table where the CompanyName includes the term ‘Inc’. It’s a concise, readable way to perform searches without resorting to regular expressions.

Conclusion

Mastering CHARINDEX equips you with a versatile tool that can handle everything from simple substring checks to complex data parsing routines. It’s one of those essential SQL functions that, while easy to start using, offers deeper utility as you explore its advanced applications.

Keep sharpening your SQL skills and experiment with combining CHARINDEX alongside other functions like SUBSTRING, LEFT, RIGHT, and PATINDEX. The richer your toolkit, the more elegant and powerful your queries will become.

Back to articles