Brettb.Com
  HOME | ABOUT ME | BIOTECHNOLOGY | ARTICLES | GALLERY | CONTACT
Search: Go
TECHNICAL ARTICLES
 ASP
 ASP.NET
 JavaScript
 Transact SQL
 Other Articles
 Software Reviews

PHOTO GALLERIES
 Canon EOS 300D Samples
 Akihabara Maids!
 More Galleries...

TRAVEL LOG
 2009: China
 2008: Tokyo
 2007: Tokyo
 2006: Hong Kong
 2005: New York City

MORE STUFF
 Search Engine Optimisation
 Build an ASP Search Engine
 My Tropical Fishtank
 Autoglass
 SQL Month Name
 SQL Get Date Today
 SQL Year Month
 Other New Stuff...

POPULAR STUFF
 Regular Expressions
 Index Server & ASP
 JavaScript Ad Rotator

Home > Articles > Transact SQL Programming Articles

Using the Transact SQL Patindex Function

The Transact SQL Patindex function can be used to return the position of the first instance of a specific pattern within a string.

Patindex Examples

The following transact SQL statement searches for customers in the Northwind SQL Server sample database that have the string 'manager' somewhere in their contact title

select CustomerID, ContactTitle, patindex('%manager%', ContactTitle) as 'ManagerPos'
from Customers
where patindex('%manager%', ContactTitle) > 0

Note that if a pattern is not matched in a string, the Patindex function returns a zero.

When using the Patindex function, the underscore character can be used to represent a wildcard of a single character. In the following SQL statement, the patindex function is used to search for customers that have a 3 character first name beginning with li. This statement will return the two customers called Liz Nixon and Liu Wong.

select CustomerID, ContactName, patindex('Li_ %', ContactName) as 'NamePos'
from Customers
where patindex('Li_ %', ContactName) > 0

String Matching Without Pattern Matching

For matching strings where wildcard pattern matching is not required, it is possible to use the Charindex function.

Useful Links

  • The SQL Documentation Tool creates technical documentation for Microsoft SQL Server databases. A free trial version is available for download.

  Site Map | Privacy Policy

All content is 1995 - 2012