LearnNowOnline

Blog

T-SQL: PATINDEX Function

Excerpt by Don Kiely | April 09, 2013

In a previous article we discussed the REPLACE and STUFF functions. This article takes a look at PATINDEX, which supports wildcard characters and other regular-expression-like features for a pattern search. PATINDEX returns the starting position of the first occurrence of a pattern in an expression, using the following syntax. Like CHARINDEX, it returns zero if it doesn't find the pattern.

PATINDEX ( '%pattern%' , expression )

PATINDEX supports the same syntax that you can use with the LIKE operator. This includes the % wildcard for one or more characters and the _ wildcard for any single character.

The following queries return different results, as shown in the Figure below, because they use different wildcards.

SELECT ProductName, QuantityPerUnit

FROM dbo.Products

WHERE PATINDEX('24 - % g pkgs.', QuantityPerUnit) > 0;

SELECT ProductName, QuantityPerUnit

FROM dbo.Products

WHERE PATINDEX('24 - __ g pkgs.', QuantityPerUnit) > 0;

In addition to wildcards, PATINDEX supports the use of square brackets to indicate ranges of characters to match. Use a caret (^) inside the brackets to indicate that the match is based on NOT finding the specified range of characters.

For example, you could use the following query instead of ISNUMERIC if you don't want to return rows with numeric values like "123d4" or "123e4." This query filters out all rows where PostalCode contains any characters other than the digits 0-9: Literally, it finds strings that don't contain any characters that aren't numbers. Removing the double negative from that last sentence, it returns only rows where the PostalCode contains only numbers.

SELECT PostalCode

FROM dbo.Customers

WHERE PATINDEX('%[^0-9]%',PostalCode)=0;

Another advantage of PATINDEX over CHARINDEX is that you can use it with text, ntext, and image columns. The Figure below shows the results from the following example, which finds all Descriptions that contain 'sweet' and the position in each string.

SELECT CategoryName, Description,

PATINDEX('%sweet%', Description)

FROM dbo.Categories

WHERE PATINDEX('%sweet%', Description) > 0;

This post is an excerpt from the online courseware for our SQL Server 2012: Introduction to T-SQL course written by expert Don Kiely.