In SQL Server, you can use either the CHARINDEX() function or the PATINDEX() function to find a string within a string. These are Transact-SQL string functions, and they’re also available on Azure databases.
On the surface, these functions appear to do exactly the same thing, and in many cases, you could use whichever you prefer to use.
However, there a a couple of distinctions that could dictate which function you decide to use in certain scenarios. These can be summarized by the following:
PATINDEX()allows you to use wildcard characters to search for patterns.CHARINDEX()doesn’t.CHARINDEX()accepts a third argument which allows you to specify the start position of the search.PATINDEX()doesn’t.
More detail on these points below.
Definition
First, let’s look at the official definition or each function.
CHARINDEX()- Searches for one character expression inside a second character expression, returning the starting position of the first expression if found.
PATINDEX()- Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.
Syntax
And here’s the official syntax of each function.
CHARINDEX()
CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )
PATINDEX()
PATINDEX ( '%pattern%' , expression )
So both functions allow you to search for a character expression, however the PATINDEX() function allows you to search for a pattern. So this is extra functionality that you don’t get with the CHARINDEX() function. With PATINDEX() you can use wildcard characters to specify a pattern to search, which could be very handy in some situations.
However, CHARINDEX() accepts three arguments whereas PATINDEX() only accepts two. The CHARINDEX() function accepts an optional third argument that allows you to specify the starting position of the search. In other words, the CHARINDEX() function allows you to return only those matches that occur after a certain point in the string.
Examples
Here are examples that demonstrate the extra functionality provided by each function.
CHARINDEX()
Here, I provide a third argument to specify a position to start searching. In this case, it will skip the first occurrence of Bob and it will return the position of the second occurrence.
SELECT CHARINDEX('Bob', 'Bob likes beer. Bob also likes beef.', 16);
Result:
17
So as mentioned, you can’t do this with PATINDEX() because it doesn’t accept that third parameter.
PATINDEX()
Now here’s an example of something you can do with PATINDEX() that you can’t do with CHARINDEX(). In this example, we use wildcard characters to search for a pattern:
SELECT PATINDEX('%b_er%', 'Bob likes beer.');
Result:
11
In this case, we use the percent signs (%) which are wildcards to indicate that any number of characters could proceed and succeed our search string. We also use the underscore (_) which is a wildcard for any single character.
Conclusion
So while both CHARINDEX() and PATINDEX() provide similar functionality, and in many cases, either one could be used instead of the other, there are times where you’d need to use one over the other.
Specifically, you’d use CHARINDEX() when you want to specify a starting position within the string to search. And you’d use PATINDEX() whenever you need to specify a pattern to search for.