SQL SERVER – Difference Between CHARINDEX and PATINDEX with example

CHARINDEX or PATINDEX can be use to search specify pattern in table column in sql server.
Difference between CHARINDEX and PATINDEX is one use wild card character to search pattern in sql server
CHARINDEX and PATINDEX gives you the starting position of a pattern in STRING or Experssion.

Example of CHARINDEX

SELECT CHARINDEX(‘Hello’,’Hello World’) as ‘Result of CHARINDEX’
Here, We are looking for “Hello” word in given string “Hello World”. CHARINDEX Returns position of “Hello” as output.
Output
Result of CHARINDEX
——————–
1

Example of PATINDEX

Both functions takes two parameter. In PATINDEX, you have to include Wild card charcater that is
percent (%) before and after the pattern unlike CHARINDEX fucntion can not include percent.
SELECT PATINDEX(‘%Hello%’,’Hello World’) as ‘Result of PATINDEX’
Output
Result of PATINDEX
——————–
1
Both function returns same value, only difference is PATINDEX uses wild card whereas CHARINDEX not.

Leave a Reply

  

  

  


9 + 1 =