PATINDEX Function in SQL

PATINDEX is a function in SQL Server that returns the starting position of a specified pattern in a given expression (string). If the pattern is not found, it returns 0.

Syntax:

PATINDEX('%pattern%', expression)

• The pattern can include wildcards (%, _).

• Returns the position (1-based index) where the pattern starts.

• Returns 0 if the pattern is not found.

Example:

Find the position of the word "cat" in a string:

SELECT PATINDEX('%cat%', 'The black cat sat on the mat') AS Position;

Result:

Position--------11

Here, "cat" starts at the 11th character in the string.

Use case:

• Useful to locate a pattern inside a string.

• Often used with conditional logic (CASE, WHERE).

Notes:

• Similar to CHARINDEX, but PATINDEX supports wildcards.

• Available only in SQL Server (T-SQL).

If you want, I can also explain CHARINDEX or pattern matching with LIKE.