The SQL PATINDEX function searches an input expression for a given pattern. It returns the starting index of the first occurrence of the pattern found in the input expression.

Description

The PATINDEX function is very similar to the LIKE function in SQL. However, it returns the starting position of the first occurrence of the pattern in the input string. The search is case-sensitive. The PATINDEX starts indexing from position 1.

SQL PATINDEX Usage Notes

The first argument in the PATINDEX function is the pattern to be searched in the input string. The second argument is the input expression to be searched for the given pattern. The data type of the pattern and input expression is of character string category.

If the pattern is not present, the PATINDEX function returns 0. If the input expression or the pattern is NULL, the PATINDEX function returns a NULL. The maximum limit of characters in a pattern is 8000.

If the data type of the input expression is varchar(max) or nvarchar(max), then the return data type is bigint, otherwise int.

The pattern encloses in %%. We can use Wildcards in the pattern like,

  • an underscore _ to match a single character
  • braces [] to match any character in the square brackets
  • [^] to match any character, not in the square brackets
  • percent % to match any string of any length

Syntax

PATINDEX (‘%pattern%’, input_expression)

SQL PATINDEX Examples

The following example shows a simple use of the PATINDEX function.

SELECT PATINDEX('%Index%', 'PatIndex Function') Position; 
/* Answer */
SELECT PATINDEX('%Index%', 'PatIndex Function') Position; 

The above query returns the starting index of the pattern Index in the input string.

The following queries use the AdevnetureWorks2019 database.

We can use the PATINDEX function to find a specific value in the column.

SELECT Description, PATINDEX('%alloy%', Description) MetalPosition FROM Production.ProductDescription
/* Answer */
SELECT Description, PATINDEX('%alloy%', Description) MetalPosition
 FROM Production.ProductDescription

The above query returns the starting position of the word alloy in the product description column. The function returns zero if there is no alloy word in the product description value.

The following query shows how to check an unwanted value in the database column.

SELECT CarrierTrackingNumber, PATINDEX('%[^ 0-9A-z-]%', CarrierTrackingNumber) InvalidTrackingNumberCharacter FROM Sales.SalesOrderDetail
/* Answer */
SELECT CarrierTrackingNumber,
PATINDEX('%[^ 0-9A-z-]%', CarrierTrackingNumber) InvalidTrackingNumberCharacter
FROM Sales.SalesOrderDetail

The PATINDEX function checks for a pattern that is not an alphabet, number, or underscore. The valid carrier tracking number comprises alphanumeric values separated by an underscore.

The following example uses a wildcard with the pattern.

SELECT Description, PATINDEX('%Dis_%', Description) DiscountDescription FROM Sales.SpecialOffer
/* Answer */
SELECT Description, PATINDEX('%Dis_%', Description) DiscountDescription
FROM Sales.SpecialOffer

The result shows the staring index of pattern ‘Dis_’.  The underscore (_) symbol matches all the patterns that start with Dis. The output contains all special offers on a discount.

See Also

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Are YOU READY TO START LEARNING SQL SERVER, BUT DON'T KNOW WHERE TO START?

Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

But it doesn't have to be this way.

I'm Putting together a free email course to help you get started learning SQL Server.

Name*
Email*
>