Pattern matches are used where one may not know what an exact match is. Pattern matches require the use of the LIKE operator with wildcard characters
and manipulate string data.
Sample query:
Select RowID, TextDescription FROM SomeTable Where TextDescription _______________
To follow along I have a script file, “PatternMatching and the LIKE Operator.sql“
| Wildcard pattern | Possibly valid results | Possible invalid results | Reason? |
| LIKE ‘YE%’ | YELLOW, yellow, yes | yellow, if a case-sensitive collation was used. | |
| LIKE ‘ye%’ | YELLOW, yellow, yes | YELLOW, if a case-sensitive collation was used. | |
| LIKE ‘%een’ | green, Halloween | greenish | |
| LIKE ‘%en%’ | spent, entertain, hen | next | |
| LIKE ‘_en’ | hen | Glenn | |
| LIKE ‘[CK]%’ | {Starts with either C or K followed by anything} cost, kick | send | |
| LIKE ‘[H-M]an%’ | {Starts with any letter in the range of H through M} hand, Jansport, man Could also combine this technique, LIKE ‘[H-M][H-M][H-M]246%’ to find HJJ246a or MJI246b | grand, sand, stand | |
| LIKE ‘M[^c]%’ | MacDonald, Micropolis | McDonald | |
| LIKE ‘Sm_th’ | Smith, Smyth | Smythe, Smooth |
LIKE can be combined with other expressions using logical operators such as:
Select RowID, TextDescription
FROM SomeTable
Where TextDescription LIKE ‘%een’
OR
TextDescription LIKE ‘M[^c]%’
Also check these out:
- SQL Server LIKE Examples by Ben Snaidero at MSSQLTips
- Trigram Wildcard String Search in SQL Server by Paul White at SQLServerPerformance.com. Very detailed.