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.