Skip to content
Related Articles
Get the best out of our app
GeeksforGeeks App
Open App
geeksforgeeks
Browser
Continue

Related Articles

SQL Server – PATINDEX() Function

Improve Article
Save Article
Like Article
Improve Article
Save Article
Like 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.

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

My Personal Notes arrow_drop_up
Last Updated : 23 Sep, 2021
Like Article
Save Article
Similar Reads