SQL CHARINDEX Vs PATINDEX Function

The primary distinction between the CHARINDEX and PATINDEX functions will be discussed in this article.

SQL CHARINDEX FUNCTION

CHARINDEX : It's a string function that returns the pattern's starting location.

If a character expression is found inside a second character expression, this method returns the first expression's starting point.

Example 1: To find index position of character

--sql-server

SELECT CHARINDEX('the', 'the doctor uses the stethoscope') As 'Find index of the',
CHARINDEX('the', 'the doctor uses the stethoscope',10) As 'Skip 10 characters and Find index of the';

--Mysqlserver

SELECT LOCATE("doctor" , "the doctor uses the stethoscope") As 'Find index of doctor',
LOCATE("doctor", "the doctor uses the stethoscope",2) As 'Skip 2 characters and Find index of doctor';

--sqlite

SELECT instr('doctor' , 'the doctor uses the stethoscope') As 'Find index of doctor';

Output: We find index of character in above query so the output is

Find index of doctor	Skip 2 characters and Find index of doctor
5	5

This isn't possible with PATINDEX() because the third parameter isn't accepted.

SQL PATINDEX Function

It's a str function that returns the pattern's starting location. When looking for a pattern, we can also employ wildcard characters.

The position of the first occurrence of a pattern in a string is returned by this method.

It allows you to search another string for a very complex string pattern.

Example 1: PATINDEX function

--sql-server

SELECT PATINDEX('%A%', 'RADHAKRISHNA');

Output : The output of patindex will be

2 It will inform you of your position as the first occurrence of A

Example 2: In this case, we're looking for a pattern using wildcard characters

Here's an example of something PATINDEX() can achieve that CHARINDEX can't().

--sql-server

SELECT PATINDEX('%S__ver%', 'SQL Server ') position;

Output: The output of patindex with wildcard character is

In this situation, we're using percent signs (%) as wildcards to indicate that any number of characters could come before or after our search phrase.

We also employ the underscore (_), which is a wildcard character that can be used to represent any single character.

we obtained the same result as the CHARINDEX() function. I know you're probably wondering why you need to use the PATINDEX() function in the first place.

If you don't know the exact substring you're looking for, the PATINDEX() method comes in helpful. With the use of wildcard, PATINDEX will allow you to use pattern to search for expression in another expression.

SQL Difference Between CHARINDEX AND PATINDEX Function

Here we discuss the main distinguish between charindex & patindex and points in follow below table:-

CHAINDEX	PATINDEX
Returns the beginning point of the supplied Expression in a Character String.	Comes back the Starting Position of the First Occurrence of a pattern in the Specified Expression, or 0 if no pattern is detected.
You can define the Start location in Charindex.	You can't define a beginning place in PatIndex. It will provide you the First Occurrence of a Pattern result.
Wildcard characters are not allowed in CHARINDEX().	PATINDEX() allows you to search for patterns using wildcard characters.
The third input to CHARINDEX() allows you to specify the search's start position.	The third argument is not accepted by PATINDEX().
When you want to define a beginning place within the string to search, use CHARINDEX().	When you need to define a pattern to look for, use PATINDEX().
In SQL Server, it can be used to search, control, and analyse text.	In SQL Server, it can be used to search, control, and analyse text.

All Tutorials