SQL Server PATINDEX() Function

The PATINDEX() function in SQL Server searches for a pattern within a string and returns the starting position of the first match. It's a powerful tool for finding specific patterns within text data.

PATINDEX(): Definition and Usage

PATINDEX() is very useful for flexible text searching. It's case-insensitive and uses wildcard characters to define the pattern you're looking for. The pattern must be enclosed in percentage signs (%). If no match is found, it returns 0. The first position in the string is 1.

Syntax


PATINDEX('%pattern%', string)

Parameter Values

Parameter	Description
`%pattern%`	The pattern to search for. This is required and must be enclosed in percentage signs (%). Wildcards can be used within the pattern (see below).
`string`	The string to search within. This is required.

Wildcard Characters

%: Matches any sequence of zero or more characters.
_: Matches any single character.
[...]: Matches any character within the brackets.
[^...]: Matches any character *not* within the brackets.

Examples

Finding a Pattern

This example searches for "schools" (case-insensitive) in "W3Schools.com".

Syntax


SELECT PATINDEX('%schools%', 'W3Schools.com');

Output

Using Multiple Wildcards

This example uses multiple wildcards to find a pattern that includes 's' and 'com'.

Syntax


SELECT PATINDEX('%s%com%', 'W3Schools.com');

Output

Using Character Sets

This example searches for either 'o' or 'l' within the string.

Syntax


SELECT PATINDEX('%[ol]%', 'W3Schools.com');

Output

Searching for a Non-Existent Pattern

Searching for 'z' (which is not present).

Syntax


SELECT PATINDEX('%[z]%', 'W3Schools.com');