RSS Feed

SQL Server PATINDEX Examples

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