SQL PATINDEX Function

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

SQL PATINDEX Function Syntax

The syntax of the PATINDEX in SQL Server is

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

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

SQL PATINDEX Function Example 1

We will show multiple ways to use the PATINDEX function.

-- SQL PATINDEX example
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 of SQL
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 PATINDEX function to find the index position of ‘a’ from the variable @str1.

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

Next, we are looking for xyz index position

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

Although it has xyz at multiple positions, the PATINDEX returned value 8. It is because SQL Server 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

PATINDEX Function example 2

The PATINDEX function 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 PATINDEEX example, We use this data.

SQL PATINDEX Function 2

PATINDEX CODE

-- PATINDEX in SQL example
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
SQL PATINDEX Function 3