Transact SQL Other Articles Software Reviews
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.
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'
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
String Matching Without Pattern Matching
For matching strings where wildcard pattern matching is not required, it is possible to use the Charindex function.