SQL Server Articles

Submit Article
Home » Articles » SQL Server » String FunctionsRSS Feeds


Posted By: Sarita Patel     Category: SQL Server     Views: 30142

This article explains PATINDEX function of sql server with examples.

PATINDEX is used to get starting position of the first occurrence of a pattern in a specified expression. 

Syntax of PATINDEX Function :

PATINDEX ( '%pattern%' ,expression )

pattern is a sequence of characters to be found from expression. We can specify wildcard characters in pattern. It can be of short character data type.

expression is a string from which pattern to be searched. It is of character string data type. 

It returns an integer value.

Examples of PATINDEX Function :

Example 1 : Use of PATINDEX function in select clause

SELECT PATINDEX('%-E%','Syntax-Example') 


Above example returns position of starting index for characters '-E' in specified string. 

SELECT PATINDEX('%l_%','Syntax-Example') 

Above example returns position of starting index for characters 'l' followed by any character in specified string. Here '%' and '_' are wildcard characters used.

Example 2 : Use of PATINDEX function to display field value of table

SELECT Address, PATINDEX('% %',Address) AS 'Index'
FROM    Customers

ContactName                             Index
Obere Str. 57                                6
Avda. de la Constitución 2222       6
Mataderos  2312                          10
120 Hanover Sq.                            4
Berguvsvägen  8                           13
Forsterstr. 57                               12

Above example displays starting position of first space in address field from customers table.

Example 3 : Use of PATINDEX function in where clause

SELECT ContactName, Address
FROM    Customers
WHERE  PATINDEX('%8%',Address) > 0

ContactName            Address
Christina Berglund     Berguvsvägen  8
Diego Roel                C/ Moralzarzal, 86
Martine Rancé           184, chaussée de Tournai
Carlos Hernández      Carrera 22 con Ave. Carlos Soublette #8-35
Patricia McKenna      8 Johnstown Road
Jaime Yorres             87 Polk St. Suite 5

Above example displays all customers containing character '8' in addresss from customers table.



Didn't find what you were looking for? Find more on PATINDEX Function Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of PATINDEX Function is from United States.
View All Articles

Related Articles and Code:

Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

No Comment Found, Be the First to post comment!