Open In App

SQL Server – PATINDEX() Function

Last Updated : 23 Sep, 2021
Improve
Improve
Like Article
Like
Save
Share
Report

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


Like Article
Suggest improvement
Share your thoughts in the comments