This tutorial aims to guide you in understanding how to use the PATINDEX function in SQL Server. This function allows you to determine the start position of a pattern in a given input expression.
SQL Server Patindex() Function
The following code snippet defines the syntax of the PATINDEX() function in SQL Server:
The arguments are explored below:
- pattern – this argument defines the character expression to be searched in the expression. This value supports wildcard characters such as % and _. The function will apply the wildcard characters similarly to the LIKE operator. You can only provide a maximum of 8000 characters.
- expression – this defines the expression where the pattern is searched. This can be a literal value or a column.
The function will then return an integer value denoting the starting position of the first occurrence pattern in the expression. If the pattern is not found in the expression, the function returns 0.
If either of the required arguments is NULL, the function will automatically return NULL.
Example Usage
The following examples illustrate how to use the patindex() function in SQL Server.
Example 1 – Basic Usage
Below is a demonstration of the basic usage of the patindex() function.
This should return the start position of the found pattern as:
13
Example 2
In the example below, we are using the patindex() function with multiple wildcard characters.
In this case, the function should return:
9
Example 3 – Using Patindex() Function with Complex Pattern
We can also pass a complex regular expression as the pattern in the patindex function as shown:
Result:
21
Example 4 – Using the Patindex Function with Column
Suppose we have a table as illustrated below:
We can use the patindex() function to search matching pattern in the product_name column as shown in the query below:
from products
This should return the position of the matching pattern as shown:
Conclusion
In this tutorial, we covered the fundamentals of working with PATINDEX() function in SQL Server.
