Summary: This SQL Server tutorial explains how to use the PATINDEX function in the SQL Server (Transact-SQL) with syntax and examples to identify the position of a pattern during a string.
Definition and Usage
The PATINDEX() function In SQL Server (Transact-SQL) returns the starting position of the first occurrence of a pattern in a string, or zeros if the pattern is not found, on all valid text and character data types. The search is not case-sensitive.
There are following syntax as given below:
In this syntax:
These function accepts two arguments:
Required. the pattern is a character expression to be found. It will contain wildcard characters like % and '_' in the pattern. The meanings of the wildcards are the same as they’re used with the LIKE operator.
Required. the string is a character string in which the pattern to be searched.
The PATINDEX() function in SQL Server returns an integer that specifies the position of the first occurrence of the pattern in the string or zero if the pattern not found. The PATINDEX() function will return NULL if either pattern or string is NULL.
Note:Important Note: The PATINDEX() searches for the pattern supported on the collation of the input. If you wish to use a specific collation, you’ll be able to use the COLLATE clause explicitly.
SQL Server (starting with 2008), Azure SQL Database, Azure Synapse Analytics, Azure SQL Managed Instance, Parallel Data Warehouse
SQL PATINDEX() Function Examples
Let’s take see the examples, How to use these function.
A) SQL Server PATINDEX() General Example
This example returns the starting position of the substring ‘ern’ in the string ‘SQL Pattern Index’:
Result:7--(search isnotcase-sensitive so it will match on'alSc')
Result:2--(matches on the firsta,e,i,o,orucharacter found)
B) Using PATINDEX() Function With Multiple Wildcards Example
In this example we will use the % and _ wildcards to find the position at which the pattern 't', followed by any two characters and 'orial' starts in the ‘Learn SQL Tutorial’ string:
Note:Patindex function is identical to the like operator. The SQL Server Patindex function returns an int. The Patindex function in SQL Server returns the beginning or starting position of the first occurrence of a pattern during a specific statement or expression, or zeros if the pattern isn’t found. Patindex also can use wildcard characters. Furthermore, the SQL Server Patindex function operates on text, ntext, varchar, nvarchar, char, nchar, varchar, and nvarchar, data types only.
In This SQL Server tutorial, you have learned how to use the PATINDEX() function in the SQL Server (Transact-SQL) with syntax and examples to identify the position of a pattern during a string.