Blog

T-SQL: PATINDEX Function

Excerpt by Don Kiely

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;

PATINDEXIn 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;

PostalCodes with only numeric values

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;

Products that are sweet

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



Don Kiely
Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor, and consultant specializing in Microsoft technologies. Don has many years of teaching experience, is the author or co-author of several programming books, and has spoken at many industry conferences and user groups. In addition, Don is a consultant for a variety of companies that develop distributed applications for public and private organizations.


This course excerpt was originally posted April 09, 2013 from the online courseware SQL Server 2012, Part 7 of 9: Introduction to T-SQL by Don Kiely

SQL Server