PATINDEX (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
Returns the starting position of the first occurrence of a pattern in a specified expression, or zero if the pattern is not found, on all valid text and character data types.
Transact-SQL syntax conventions
Syntax
PATINDEX ( '%pattern%' , expression )
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Arguments
pattern
Is a character expression that contains the sequence to be found. Wildcard characters can be used; however, the % character must come before and follow pattern (except when you search for first or last characters). pattern is an expression of the character string data type category. pattern is limited to 8000 characters.
Note
While traditional regular expressions are not natively supported in SQL Server, similar complex pattern matching can be achieved by using various wildcard expressions. See the String Operators documentation for more detail on wildcard syntax.
expression
Is an expression, typically a column that is searched for the specified pattern. expression is of the character string data type category.
Return Types
bigint if expression is of the varchar(max) or nvarchar(max) data types; otherwise int.
Remarks
If either pattern or expression is NULL, PATINDEX returns NULL.
The starting position for PATINDEX is 1.
PATINDEX performs comparisons based on the collation of the input. To perform a comparison in a specified collation, you can use COLLATE to apply an explicit collation to the input.
Supplementary Characters (Surrogate Pairs)
When using SC collations, the return value will count any UTF-16 surrogate pairs in the expression parameter as a single character. For more information, see Collation and Unicode Support.
0x0000 (char(0)) is an undefined character in Windows collations and cannot be included in PATINDEX.
Examples
A. Simple PATINDEX example
The following example checks a short character string (interesting data
) for the starting location of the characters ter
.
SELECT position = PATINDEX('%ter%', 'interesting data');
Here is the result set.
position
--------
3
B. Using a pattern with PATINDEX
The following example finds the position at which the pattern ensure
starts in a specific row of the DocumentSummary
column in the Document
table in the AdventureWorks2022 database.
SELECT position = PATINDEX('%ensure%',DocumentSummary)
FROM Production.Document
WHERE DocumentNode = 0x7B40;
GO
Here is the result set.
position
--------
64
If you do not restrict the rows to be searched by using a WHERE
clause, the query returns all rows in the table and reports nonzero values for those rows in which the pattern was found, and zero for all rows in which the pattern was not found.
C. Using wildcard characters with PATINDEX
The following example uses % and _ wildcards to find the position at which the pattern 'en'
, followed by any one character and 'ure'
starts in the specified string (index starts at 1):
SELECT position = PATINDEX('%en_ure%', 'Please ensure the door is locked!');
Here is the result set.
position
--------
8
PATINDEX
works just like LIKE
, so you can use any of the wildcards. You do not have to enclose the pattern between percents. PATINDEX('a%', 'abc')
returns 1 and PATINDEX('%a', 'cba')
returns 3.
Unlike LIKE
, PATINDEX
returns a position, similar to what CHARINDEX
does.
D. Using complex wildcard expressions with PATINDEX
The following example uses the [^]
string operator to find the position of a character that is not a number, letter, or space.
SELECT position = PATINDEX('%[^ 0-9A-Za-z]%', 'Please ensure the door is locked!');
Here is the result set.
position
--------
33
E. Using COLLATE with PATINDEX
The following example uses the COLLATE
function to explicitly specify the collation of the expression that is searched.
USE tempdb;
GO
SELECT PATINDEX ( '%ein%', 'Das ist ein Test' COLLATE Latin1_General_BIN) ;
GO
Here is the result set.
position
--------
9
F. Using a variable to specify the pattern
The following example uses a variable to pass a value to the pattern parameter. This example uses the AdventureWorks2022 database.
DECLARE @MyValue VARCHAR(10) = 'safety';
SELECT position = PATINDEX('%' + @MyValue + '%', DocumentSummary)
FROM Production.Document
WHERE DocumentNode = 0x7B40;
Here is the result set.
position
--------
22
See Also
LIKE (Transact-SQL)
CHARINDEX (Transact-SQL)
LEN (Transact-SQL)
Data Types (Transact-SQL)
String Functions (Transact-SQL)
(Wildcard - Character(s) to Match) (Transact-SQL)
(Wildcard - Character(s) Not to Match) (Transact-SQL)
_ (Wildcard - Match One Character) (Transact-SQL)
Percent character (Wildcard - Character(s) to Match) (Transact-SQL)