Patindex Function and Charindex Function in SQL Server

Question

Hi all, What is the difference between Patindex Function and Charindex Function in SQL Server.

Santhosh Kumar Jayaraman · Answer

e can use either CHARINDEX or PATINDEX to search in TEXT field in SQL SERVER. The CHARINDEX and PATINDEX functions return the starting position of a pattern you specify. Both functions take two arguments. With PATINDEX, you must include percent signs before and after the pattern, unless you are looking for the pattern as the first (omit the first %) or last (omit the last %) characters in a column. For CHARINDEX, the pattern cannot include wildcard characters. The second argument is a character expression, usually a column name, in which Adaptive Server searches for the specified pattern. Example of CHARINDEX: USE AdventureWorks ; GO SELECT CHARINDEX ( 'ensure' , DocumentSummary ) FROM Production.Document WHERE DocumentID = 3 ; GO Examples of PATINDEX: USE AdventureWorks ; GO SELECT PATINDEX ( '%ensure%' , DocumentSummary ) FROM Production.Document WHERE DocumentID = 3 ; GO Summary: PATINDEX is CHARINDEX + WildCard Search. Use either of them depending your need. Reference: http://blog.sqlauthority.com/2007/04/08/sql-server-search-text-field-charindex-vs-patindex/

Patindex Function and Charindex Function in SQL Server

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (1)