SQL Server PATINDEX Examples
You can have a look at the different examples of PATINDEX and get an in-depth understanding of working of PATINDEX.
Example 1 of PATINDEX
This example checks a character string (SQL SERVER) for the starting location of the characters "ver".
SELECT PATINDEX('%ver%', 'SQL SERVER');
Result:
(No column name)
8
Example 2 of PATINDEX
Select * from SSS
| ID | Name |
|---|---|
| 1 | aaa |
| 2 | bbb |
| 3 | aaa |
| 4 | bbb |
| 5 | cat |
| 6 | zzz |
| 7 | India |
This example finds the position at which the pattern "ia" starts in all rows of the Name column in the SSS table.
SELECT PATINDEX('%ia%',Name)
FROM SSS
GO
Result:| (No column name) |
|---|
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 4 |
This example finds the position at which the pattern "ia" starts in a specific row of the Name column in the SSS table.
SELECT PATINDEX('%ia%',Name)
FROM SSS
WHERE ID = 7;
GO
Result:
(No column name)
4
Example 3 of PATINDEX
Following example uses % and _ wildcards to find the position at which the pattern, starting by any one character and followed by 'g', starts in the specified string (index starts at 1):
SELECT PATINDEX('%_g%', 'A QUICK BROWN FOX JUMPS OVER THE LAZY DOG. Hey its fog today!');
Result: (No column name) 40
Example 4 of PATINDEX
This example uses a variable to pass a value to the pattern parameter. It uses the same table as used in example 2.
DECLARE @MyValue varchar(10) = 'ia';
SELECT PATINDEX('%' + @MyValue + '%', Name)
FROM SSS
WHERE ID = 7;
Result:
(No column name)
4
Example 5 of PATINDEX
To find the position of the first occurrence of a digit within a string.
SELECT PATINDEX('%[0-9]%', 'volkxmk834hs');
Result:
(No column name)
8
Example 6 of PATINDEX
Pattern to search for "MD" or "DR" in the given string
select PATINDEX ('%[MD][DR]%','Mashoor Gulati MD')
Result:
(No column name)
16
select PATINDEX ('%[MD][DR]%','Mashoor Gulati DR')
Result:
(No column name)
16
Example 7 of PATINDEX
Example uses the COLLATE function to explicitly specify the collation of the expression that is searched.
SELECT PATINDEX ( '%A%', 'aA' COLLATE SQL_Latin1_General_Pref_CP850_CI_AS); Result: (No column name) 1 SELECT PATINDEX ( '%A%', 'aA' COLLATE SQL_EBCDIC297_CP1_CS_AS); Result: (No column name) 2 SELECT PATINDEX ( '%A%', 'aA') ; Result: (No column name) 1