SQL PATINDEX

The SQL Server PATINDEX is used to return the starting index position of the first occurrence of a pattern in a specified expression. If the specified string is not found, then it will return zero. The index position of the PATINDEX Function will start from 1, Not 0.

The syntax of the SQL PATINDEX function is as shown below.

SELECT PATINDEX (%pattern%, ExpressionToSearch)
FROM [Source]

%pattern%: It can be a single character or a sequence that must be found within the original string. Here, the % symbol is a wildcard, meaning there can be a few characters before and after the pattern.

Expression_to_search: It is a variable, string, or column name where the original string is present. The PATINDEX() searches for the pattern inside this string.

The PATINDEX function searches for the index position of a specific pattern from the original string. It will return NULL if either pattern or ExpressionToSearch is NULL.

SQL Server PATINDEX Example

We will show multiple ways to use this function. Please refer to the list of SQL String Functions from our SQL Server Programming.

DECLARE @str1 VARCHAR(50), @str2 VARCHAR(50)
SET @str1 = 'Tutorials On SQL Server 2014'
SET @str2 = 'We are xyz working at xyz Corporation'

SELECT PATINDEX('%a%', @str1) AS Result1

-- Finding the Index position 
SELECT PATINDEX('%sql%', @str1) AS Result2

SELECT PATINDEX('%language%', @str1) AS Result3

-- Finding the Index position of First Occurred xyz
SELECT PATINDEX('%xyz%', @str2) AS Result4

SELECT PATINDEX('%a_%', @str2) AS Result5
SQL PATINDEX Function 1

We used the SQL PATINDEX function to find the index position of ‘a’ from the variable @str1. Here, %a% means there are N characters before and after a. The query below returns 7 because PATINDEX() found a at the 7th position.

SELECT PATINDEX('%a%', @str1) AS Result1

Next, we are looking for the xyz index position. Check the SQL TRIM function.

SELECT PATINDEX('%xyz%', @str2) AS Result4

Although it has xyz at multiple places, it has returned a value of 8. It is because the PATINDEX will return the index position of a first occurrence, and it does not care about others. In the next line, we searched for language, which does not exist inside the @str1 variable. That’s why this String Function is returning 0.

SELECT PATINDEX('%language%', @str1) AS Result3

Use SQL PATINDEX Function on table columns

It also allows you to search for the patterns inside the column values. In this SQL Server example, We find the index position of light inside the [English Description] column. For this example, We use the DimProduct table data inside the Adventure Works DW 2014 Database.

TIP: Please refer to the SQL REPLACE and SQL SUBSTRING function.

SELECT [ProductKey]
      ,[EnglishProductName]
      ,[StandardCost]
      ,[Color]
      ,[DealerPrice]
      ,[EnglishDescription]
      ,PATINDEX('%light%', [EnglishDescription]) AS indexval
      ,[StartDate]
      ,[EndDate]
  FROM [AdventureWorksDW2014].[dbo].[DimProduct]
  WHERE [EnglishDescription] IS NOT NULL AND  [EndDate] IS NOT NULL
PATINDEX Function example 2

Using PATINDEX() with multiple wildcards

The pattern argument in the PATINDEX() function is not limited to the % wildcard; we can use all kinds of available wildcards or regular expressions to search for a substring.

In the query below, it searches for H followed by a missing single character and then followed by r, which is the second word’s starting position. If we replace the pattern with ‘%H_l%’, it returns 1 because it matches the first word..

SELECT PATINDEX('%H_r%', 'Hello Horror')
7

Use PATINDEX() to find the digit in a string

In the query below, SQL PATINDEX() looks for a digit from 0 to 9 in a given string. If it is found, it returns the starting index position. Here, 404 is present at the 14th index position, so PATINDEX() returns 14.

SELECT PATINDEX('%[0-9]%', 'Page returns 404 Error')
14

In this example, we use PATINDEX() to identify the index position of the first occurrence of a special character. The query below searches for characters other than digits from 0 to 9, blank space, or letters from A to Z (uppercase and lowercase letters). If any character does not match, it returns the index position.

SELECT PATINDEX('%[^ A-z0-9]%', 'Page Error: 404')
11
Categories SQL