Skip to content
Related Articles
Open in App
Not now

Related Articles

SQL Server – PATINDEX() Function

Improve Article
Save Article
  • Difficulty Level : Hard
  • Last Updated : 23 Sep, 2021
Improve Article
Save Article

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.


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:


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




Now if we finding the first occurrence of the letter ‘z’ in the string ‘GeeksforGeeks’.


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


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


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


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



My Personal Notes arrow_drop_up
Related Articles

Start Your Coding Journey Now!