SQL Server PATINDEX Function By Practical Example

Summary: This SQL Server tutorial explains how to use the PATINDEX function in the SQL Server (Transact-SQL) with syntax and examples to identify the position of a pattern during a string.

Definition and Usage

The PATINDEX() function In SQL Server (Transact-SQL) returns the starting position of the first occurrence of a pattern in a string, or zeros if the pattern is not found, on all valid text and character data types. The search is not case-sensitive.

Syntax

There are following syntax as given below:

PATINDEX('%pattern%', string)

1	PATINDEX('%pattern%', string)

In this syntax:
These function accepts two arguments:

Required. the pattern is a character expression to be found. It will contain wildcard characters like % and '_' in the pattern. The meanings of the wildcards are the same as they’re used with the LIKE operator.
Required. the string is a character string in which the pattern to be searched.

The PATINDEX() function in SQL Server returns an integer that specifies the position of the first occurrence of the pattern in the string or zero if the pattern not found. The PATINDEX() function will return NULL if either pattern or string is NULL.

Note:Important Note: The PATINDEX() searches for the pattern supported on the collation of the input. If you wish to use a specific collation, you’ll be able to use the COLLATE clause explicitly.

Applies To:

SQL Server (starting with 2008), Azure SQL Database, Azure Synapse Analytics, Azure SQL Managed Instance, Parallel Data Warehouse

SQL PATINDEX() Function Examples

Let’s take see the examples, How to use these function.

A) SQL Server PATINDEX() General Example

This example returns the starting position of the substring ‘ern’ in the string ‘SQL Pattern Index’:

SELECT PATINDEX('%alSc%', 'TutorialScan.com');
Result:7         --(search is not case-sensitive so it will match on 'alSc')

SELECT PATINDEX('%S_a%', 'TutorialScan.com');
Result:9  

SELECT PATINDEX('%a%com', 'TutorialScan.com');
Result:7

SELECT PATINDEX('%[aeiou]%', 'TutorialScan.com');
Result:2           --(matches on the first a, e, i, o, or u character found)

SELECT PATINDEX('%y%', 'TutorialScan.com');
Result:0

SELECT PATINDEX('%alSc%', 'TutorialScan.com');

Result:7 --(search is not case-sensitive so it will match on 'alSc')

SELECT PATINDEX('%S_a%', 'TutorialScan.com');

Result:9

SELECT PATINDEX('%a%com', 'TutorialScan.com');

Result:7

SELECT PATINDEX('%[aeiou]%', 'TutorialScan.com');

Result:2 --(matches on the first a, e, i, o, or u character found)

SELECT PATINDEX('%y%', 'TutorialScan.com');

Result:0

B) Using PATINDEX() Function With Multiple Wildcards Example

In this example we will use the % and _ wildcards to find the position at which the pattern 't', followed by any two characters and 'orial' starts in the ‘Learn SQL Tutorial’ string:

SELECT 
    PATINDEX('%t__orial%', 'Learn SQL Tutorial') position;

1 2	SELECT PATINDEX('%t__orial%', 'Learn SQL Tutorial') position;

Position
-----------
11
(1 row affected)

Position

-----------

(1 row affected)

C) Using SQL Server PATINDEX() With Table Column Example

The table looks as the following:
PATINDEX Table_column
Using PATINDEX Function

DECLARE @Name varchar(30)  
SET @Name  = 'Brayden Charlies'  
SELECT PATINDEX('%C%', @Name)

DECLARE @Name varchar(30)

SET @Name = 'Brayden Charlies'

SELECT PATINDEX('%C%', @Name)

-----------
9

1 2	----------- 9

In the following example, we use the preceding table.

Select UserId, UserName, Company, PATINDEX ('%on%', UserName) as patindexint  
from [Employee] where Company='Intuit'

1 2	Select UserId, UserName, Company, PATINDEX ('%on%', UserName) as patindexint from [Employee] where Company='Intuit'

PATINDEX with Preceding Table

Note: Patindex function is identical to the like operator. The SQL Server Patindex function returns an int. The Patindex function in SQL Server returns the beginning or starting position of the first occurrence of a pattern during a specific statement or expression, or zeros if the pattern isn’t found. Patindex also can use wildcard characters. Furthermore, the SQL Server Patindex function operates on text, ntext, varchar, nvarchar, char, nchar, varchar, and nvarchar, data types only.

Conclusion:

In This SQL Server tutorial, you have learned how to use the PATINDEX() function in the SQL Server (Transact-SQL) with syntax and examples to identify the position of a pattern during a string.

TutorialScan

SQL Tutorial