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:
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 theLIKE 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.
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’:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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:
1 2 |
SELECT PATINDEX('%t__orial%', 'Learn SQL Tutorial') position; |
1 2 3 4 |
Position ----------- 11 (1 row affected) |
C) Using SQL Server PATINDEX() With Table Column Example
The table looks as the following:
Using PATINDEX Function
1 2 3 |
DECLARE @Name varchar(30) SET @Name = 'Brayden Charlies' SELECT PATINDEX('%C%', @Name) |
1 2 |
----------- 9 |
In the following example, we use the preceding table.
1 2 |
Select UserId, UserName, Company, PATINDEX ('%on%', UserName) as patindexint from [Employee] where Company='Intuit' |
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.