SQL Server PATINDEX function

Summary: in this tutorial, you have learned how to use the SQL Server PATINDEX() function to find the position of a pattern in a string.

SQL Server PATINDEX() overview

The PATINDEX() function returns the position of the first occurrence of a pattern in a string. The syntax of the PATINDEX() function is as follows:

PATINDEX ( '%pattern%' , input_string )
Code language: SQL (Structured Query Language) (sql)

The PATINDEX() function accepts two arguments:

• pattern is a character expression to be found. It can contain wildcard characters such as % and '_' in the pattern. The meanings of the wildcards are the same as they are used with the LIKEoperator.
• input_string is a character string in which the pattern to be searched.

The PATINDEX() returns an integer that specifies the position of the first occurrence of the pattern in the input_string, or zero of the pattern not found. The PATINDEX() function will return NULL if either pattern or input_string is NULL.

Note that the PATINDEX() searches for the pattern based on the collation of the input. If you want to use a specific collation, you can use the COLLATE clause explicitly.

SQL Server PATINDEX() function examples

Let’s take some examples of using the PATINDEX() function.

A) SQL Server PATINDEX() simple example

This example returns the starting position of the substring 'ern' in the string 'SQL Pattern Index':

SELECT 
    PATINDEX('%ern%', 'SQL Pattern Index') position;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

position
-----------
9

(1 row affected)

B) Using SQL Server PATINDEX() with multiple wildcards example

This example uses % and _ wildcards to find the position at which the pattern 'f', followed by any two characters and 'ction' starts in the 'SQL Server String Function' string:

SELECT 
    PATINDEX('%f__ction%', 'SQL Server String Function') position;
Code language: SQL (Structured Query Language) (sql)

The output is as follows:

Position
-----------
19

(1 row affected)

C) Using SQL Server PATINDEX() function with table column example

This example finds the position of the first occurrence of the pattern 2018  in values of the product_name column in the production.products table from the sample database.

SELECT    
    product_name, 
    PATINDEX('%2018%', product_name) position
FROM    
    production.products
WHERE 
    product_name LIKE '%2018%'
ORDER BY 
    product_name;
Code language: SQL (Structured Query Language) (sql)

The following picture shows the partial output:

In this tutorial, you have learned how to use the SQL Server PATINDEX() function to find the position of the first occurrence of a pattern in a string.