Transact SQL Other Articles Software Reviews
String Comparisons in Transact-SQL Using the 'Like' Function
The Transact SQL Like function can be used to perform pattern matching functionality on partial strings. The function takes a single argument - the pattern to be matched for. There are a number of special characters that can be included with the pattern. The most commonly used is the % wildcard character. For example, the following SQL statement returns Products where the ProductName column contains the phrase bread.
select * from Products where ProductName like '%bread%'
Due to the wild card matching, the statement will return products such as Bread, Shortbreads and Scottish Longbreads.
Using only one wildcard will return products starting with the phrase bread:
select * from Products where ProductName like 'bread%'
This statement will, therefore, return products such as Bread and Bread puddings, but it will not return either Shortbreads or Scottish Longbreads.
Finding Non-Matching Rows
It is also possible to use the Not Like function, which will find all rows not matching the pattern. For example, the following SQL statement finds all of the products that do not contain the phrase bread:
select * from Products where ProductName not like '%bread%'
Using the Like Function in Microsoft Access Databases
The Like function is also available for queries written in Microsoft Access, but the % sign usually needs to be replaced with a *, i.e.
select * from Products where ProductName like '%pear%'
Less Specific Matching
More information about this will be added later.