PATINDEX (Transact-SQL)
Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types. For more information, see Pattern Matching in Search Conditions.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
PATINDEX ( '%pattern%' , expression )
Arguments
- pattern
Is a literal string. 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.
- 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 when the database compatibility level is 70. If the database compatibility level is 65 or earlier, PATINDEX returns NULL only when both pattern and expression are NULL.
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.
Examples
A. 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.
USE AdventureWorks;
GO
SELECT PATINDEX('%ensure%',DocumentSummary)
FROM Production.Document
WHERE DocumentID = 3;
GO
Here is the result set.
-----------
64
(1 row(s) affected)
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.
B. Using wildcard characters with PATINDEX
The following example uses wildcard characters to find the position at which the pattern en_ure starts in a specific row of the DocumentSummary column in the Document table, where the underscore is a wildcard representing any character.
USE AdventureWorks;
GO
SELECT PATINDEX('%en_ure%', DocumentSummary)
FROM Production.Document
WHERE DocumentID = 3;
GO
Here is the result set.
------------
64
(1 row(s) affected)
If you do not restrict the rows to be searched, the query returns all rows in the table and reports nonzero values for those rows in which the pattern was found.
C. 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
See Also
Reference
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)
% (Wildcard - Character(s) to Match) (Transact-SQL)