126
Part II: Building Databases and Working with Data
Result:Name
-------------------
Chain
Chain Stays
Chainring
Chainring Bolts
Chainring Nut
The following query fi nds any StateProvince name beginning with a letter between d and
f, inclusive:USE AdventureWorks;
SELECT Name
FROM Person.StateProvince
WHERE Name LIKE '[d-f]%';
Result:Name
--------------------------------------------------
Delaware
District of Columbia
Dordogne
Drome
England
Essonne
Eure
Eure et Loir
Finistere
Florida
France
The two possible methods for searching for a pattern that contains a wildcard are to either
enclose the wildcard in square brackets or put an escape character before it. The trick to
the latter workaround is that the escape character is defi ned within the LIKE expression.When using the LIKE operator, be aware that the database collation’s sort order determines
both case-sensitivity and the sort order for the range of characters. You can optionally use
the keyword COLLATE to specify the collation sort order used by the LIKE operator.Best Practice
Although the LIKE operator can be useful, it can also cause a performance hit. Indexes are based on
the beginning of a column, not on phrases in the middle of the column. If you fi nd that the application
requires frequent use of the LIKE operator, you should enable full-text indexing — a powerful indexing
method that can even take into consideration weighted words and variations of infl ections and can
return the result set in table form for joining.c06.indd 126c06.indd 126 7/30/2012 4:16:03 PM7/30/2012 4:16:03 PM
http://www.it-ebooks.info