SQL Server PATINDEX Function


By:

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
PATINDEX 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.

T-SQL patindex function numbers

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 sql server function starting with letters

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.

patindex with variables

Related Articles


Last Update: 11/8/2021




Comments For This Article





download














vote


get free sql tips
agree to terms