Any suggestions? Export (0) Print
Expand All

PATINDEX (Transact-SQL)

 

Updated: January 7, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

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.

Topic link icon Transact-SQL Syntax Conventions


PATINDEX ( '%pattern%' , expression )
-- Azure SQL Data Warehouse and Parallel Data Warehouse
PATINDEX (%pattern% ,expression )

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.

expression

Is an expression, typically a column that is searched for the specified pattern. expression is of the character string data type category.

bigint if expression is of the varchar(max) or nvarchar(max) data types; otherwise int.

If either pattern or expression is NULL, PATINDEX returns 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.

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.

The following example checks a short character string (interesting data) for the starting location of the characters ter.

SELECT PATINDEX('%ter%', 'interesting data');

Here is the result set.

3

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 AdventureWorks2012 database.

SELECT PATINDEX('%ensure%',DocumentSummary)
FROM Production.Document
WHERE DocumentNode = 0x7B40;
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.

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 PATINDEX('%en_ure%', 'please ensure the door is locked');

Here is the result set.

------------

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.

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

The following example uses a variable to pass a value to the pattern parameter. This example uses the AdventureWorks2012 database.

DECLARE @MyValue varchar(10) = 'safety'; 
SELECT PATINDEX('%' + @MyValue + '%', DocumentSummary) 
FROM Production.Document
WHERE DocumentNode = 0x7B40;

Here is the result set.

------------

22

The following example finds the position at which the pattern wheel starts in the EnglishDescription column in the dbo.DimProduct table for every row that contains the characters wheel. 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.

-- Uses AdventureWorks

SELECT ProductKey, 
    PATINDEX('%wheel%',EnglishDescription)AS StartingPosition,
EnglishDescription
FROM dbo.DimProduct
WHERE EnglishDescription LIKE '%wheel%'
ORDER BY ProductKey;

Here is a partial result set.

ProductKey  StartingPosition  EnglishDescription

----------  ----------------  ----------------------------

410         22                Replacement mountain wheel for entry-level rider.

411         22                Replacement mountain wheel for the casual to serious rider.

412         39                High-performance mountain replacement wheel.

413         24                Replacement road front wheel for entry-level cyclist.

415         8                 Strong wheel with double-walled rim.

The following example uses wildcard characters to find the position at which the pattern whe_l starts in the EnglishDescription column in the dbo.DimProduct table, where the underscore is a wildcard representing any character. 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.

-- Uses AdventureWorks

SELECT ProductKey, 
    PATINDEX('%whe_l%',EnglishDescription)AS StartingPosition,
EnglishDescription
FROM dbo.DimProduct
WHERE EnglishDescription LIKE '%wheel%'
ORDER BY ProductKey;

Community Additions

ADD
Show:
© 2016 Microsoft