The SQL Server PATINDEX() function returns the starting index of the first occurrence of a pattern in a string or a specified expression. 

It returns an integer value indicating the starting position of the first occurrence of the pattern in the expression. If the pattern is not found it returns zero, if either pattern or expression is NULL, it returns NULL.

Syntax

The SQL Server PATINDEX() function syntax is:

PATINDEX ( ‘%pattern%’ , expression )

parameters,

Parameters

PATINDEX() function takes two parameters, and both are required.

• Pattern: This is the sequence to be found in the string that must be surrounded by %. Other wildcard characters can be used in patterns. The pattern has a limit of 8000 characters. Other wildcard characters are ‘%’ , ‘-‘ , ‘[]’ , ‘[^]’.
• Expression: A string that is searched for the specified pattern.

Applicable to:

The function PATINDEX() is applicable to the following databases.

• SQL Server (all supported versions)
• Azure SQL Database
• Azure SQL Data Warehouse
• Azure SQL Managed Instance
• Azure Synapse Analytics Analytics Platform System (PDW)
• Parallel Data Warehouse

SQL Server PATINDEX() Function Examples

Let’s look at some examples of the PATINDEX() function in SQL server. Learning the PATINDEX() function with examples will help to understand the function better.

Example 1

In this example, we will find the first occurrence of ‘ek’ in the string ‘GeeksforGeeks’.

Query:

SELECT PATINDEX('%ek%', 'GeeksforGeeks');

Output:

Example 2

In this example, we are finding the first occurrence of the letter ‘z’ in the string ‘GeeksforGeeks’.

Query:

SELECT PATINDEX('%[z]%', 'GeeksforGeeks');

Output:

It returns 0 because the given string does not contain the letter ‘z.

Example 3

In this example, we are finding the first occurrence of the symbol in the string ‘How are you?’.

Query:

SELECT position = PATINDEX('%[^ 0-9A-z]%', 'How are you?');

Output: