SQL Server PATINDEX Function

By: Daniel Calbimonte

The PATINDEX function is used to get the first occurrence of a pattern from a string/expression. The function returns an integer value of where the pattern exists in the string if found otherwise it returns 0.

Syntax

PATINDEX('pattern', expression)

Parameters

• pattern - this is an expression like wildcards or other operators to find.
• expression - the string or expression we want to look for an occurrence.

Simple PATINDEX Example

The following example will show the position of the colon in the character string provided. The % is a wildcard and means any character, so the pattern we are looking for below is "anyCharacter : anyCharacter".

SELECT PATINDEX('%:%','Location: Italia') as position

Detect Numbers in a Specific Position Using PATINDEX

The following detects when a number occurs in position 4 or higher.  Again we are using % for any character and then [0-9] which means any value between 0 to 9. So the pattern is "anyCharacter anyNumber anyCharacter".

SELECT TOP (1000) [AddressID], [AddressLine1]
FROM [Person].[Address]
WHERE PATINDEX('%[0-9]%', AddressLine1) > 4

So below only records where AddressLine1 has a number starting in position 4 or later is returned.

Detect Strings that Start with a Letter Using PATINDEX

Most of the AddressLine1 records in the AdventureWorks sample database start with a number. The following query will detect rows where AddressLine1 starts with a letter in position 1.  Again we are using % for any character and then [Aa-Zz] which means any letter either upper case or lower case. So the pattern is "anyCharacter anyLetter anyCharacter".

SELECT TOP (1000) [AddressID], [AddressLine1]
FROM [Person].[Address]
WHERE PATINDEX('%[Aa-Za]%', AddressLine1) = 1

PATINDEX Example Using Variables

The next example looks for rows where the value set in a variable is found in AddressLine1. We use a variable to store the pattern and then invoke the variable as a function parameter.

DECLARE @mypattern varchar(30)='%#%'

SELECT TOP (1000) [AddressID], [AddressLine1]
FROM [Person].[Address]
WHERE PATINDEX(@mypattern, AddressLine1) > 0

Below we can see rows where # exists in AddressLine1.

Related Articles

• Examples of Patterns to Search