charindex vs patindex

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.

Published by Jean Joseph

Jean Joseph is a Database, Big Data, Data Warehouse Platform, Data Pipeline, Database Architecture Solutions Provider as well as a Data Engineer enthusiast among other disciplines.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: