SQL Server – PATINDEX() Function

The PATINDEX() function in the SQL server is used to return the starting index of the first occurrence of a pattern in a string or a specified expression. 

• It returns zero if the pattern is not found.
• It returns NULL if either pattern or expression is NULL.
• Starting index is 1.

Syntax:

PATINDEX ( '%pattern%' , expression )

There are 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

Suppose if we want to find the first occurrence of ‘ek’ in the string ‘GeeksforGeeks’.then query will be like:

Query:

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

Output:

3

Now if we 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.

0

Take another example for finding the first occurrence of the symbol in the string ‘How are you?’.

Query:

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

Output:

12