This post explains and demonstrates the main different between CHARINDEX() and PATINDEX().
Keep in mind that CHARINDEX() function allows you to search for a substring in a string whereas PATINDEX() allows you to search for a substring and a pattern in a string. Both return 1 if found and 0 if not found.
CHARINDEX()
This function searches for one character expression inside a second character expression, returning the starting position of the first expression if found.
CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )
Examples 1: let’s say you want to know the position of “great” from “This is a great post” string.
SELECT CHARINDEX('great','This is a great post') 'CHARINDEX'
Example 2: So now, let’s extract “great” from “This is a great post” string.
DECLARE @string VARCHAR(50) = 'This is a great post'
SELECT SUBSTRING(@string,CHARINDEX('great',@string),5) 'CHARINDEX'
GO
PATINDEX()
This function returns the position of the first occurrence of a pattern in a string. Its allow you to search for very complex string pattern in another string.
PATINDEX ( '%pattern%' , expression )
Examples 1: let’s say you want to know the position of “great” from “This is a great post” string using PATINDEX().
SELECT PATINDEX('%great%','This is a great post') 'PATINDEX'
We got the same result set as CHARINDEX() function as you can see from above screenshot. I know you probably wondering and asking yourself, why do I need to use PATINDEX() function for.
PATINDEX() function is very handy if you do not know exactly the substring you are looking for. PATINDEX will allows you to use pattern to search for expression in another expression with the help of wildcard.
| Parameter | Description |
|---|---|
| %pattern% | Required. The pattern to find. It MUST be surrounded by %. Other wildcards can be used in pattern, such as: % – Match any string of any length (including 0 length) _ – Match one single character [] – Match any characters in the brackets, e.g. [xyz] [^] – Match any character not in the brackets, e.g. [^xyz] |
Example 2: So now, let’s consider that we want to extract anything that contain key words like “big”, “driven” and end with “org”. Note that, our goal is to extract “bigdatadriven.org” from string or within our ETL process.
DECLARE @string VARCHAR(50) = 'Check out bigdatadriven.org if you want to be updated'
SELECT SUBSTRING(@string,PATINDEX('%big%driven%org%',@string),17) 'CHARINDEX'
Conclusion:
Both CHARINDEX() and PATINDEX() will help you determine the first position of any given substrings however PATINDEX is very handy if you do not know what exactly you are looking for. PATINDEX supports wildcard whereas CHARINDEX does not.
bigdatadriven.org 