Interview Question of the Week #029 – Difference Between CHARINDEX vs PATINDEX

This interview question needs a short answer before the syntax: one function looks for literal text, and the other looks for a wildcard pattern. If the underscore is supposed to mean “any one character,” that difference matters immediately.

Question: What is the difference between CHARINDEX and PATINDEX in SQL Server?

Answer: CHARINDEX searches for a literal substring and may start at a specified position. PATINDEX searches using the familiar LIKE wildcards, including % for any sequence and _ for one character. It has no separate start-position argument. Both return a one-based position, or zero when there is no match.

Here is a small example you can run without a sample database. The string contains important and impXrtant. A wildcard pattern matches the first form at position 4; a literal underscore is absent.

DECLARE @Text nvarchar(100) = N'An important item and an impXrtant item.';
SELECT CHARINDEX(N'important', @Text) AS LiteralMatch,
       CHARINDEX(N'important', @Text, 10) AS LaterLiteralMatch,
       PATINDEX(N'%imp_rtant%', @Text) AS WildcardMatch,
       CHARINDEX(N'imp_rtant', @Text) AS LiteralUnderscoreMatch;

The same comparison works on real data. AdventureWorks2025 has a Production.Document table with text summaries:

USE AdventureWorks2025;
GO
SELECT DocumentSummary,
       CHARINDEX('important', DocumentSummary) AS LiteralMatch,
       PATINDEX('%imp_rtant%', DocumentSummary) AS PatternMatch
FROM Production.Document
WHERE Title = 'Repair and Service Guidelines'
  AND DocumentSummary IS NOT NULL;

Both functions return NULL when the searched text is NULL, so the filter keeps the result readable. Collation still affects case and accent comparisons. If the search value contains a literal percent or underscore, keep it literal with CHARINDEX or escape it deliberately in the pattern. Use the function that matches the question you actually mean to ask.

Want to try this yourself? Here is how to install AdventureWorks.

CHARINDEX is not a weaker PATINDEX, it is the literal search, and the underscore is where the two part ways.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.