Joe Celko s SQL for Smarties - Advanced SQL Programming P30. In the SQL database community, Joe Celko is a well-known columnist and purveyor of valuable insights. In Joe Celko's SQL for Smarties: Advanced SQL Programming, he picks up where basic SQL training and experience leaves many database professionals and offers tips, techniques, and explanations that help readers extend their capabilities to top-tier SQL programming. Although Celko denies that the book is about database theory, he nevertheless alludes to theory often to buttress his practical points. This title is not for novices, as the author points out. Instead, its intended audience. | 262 CHAPTER 12 LIKE PREDICATE The escape character is used in the pattern to specify that the character that follows it is to be interpreted literally rather than as a wildcard. This means that the escape character is followed by the escape character itself an _ or a . Old C programmers are used to this convention where the language defines the escape character as so this is a good choice for SQL programmers too. Tricks with Patterns The _ character tests much faster than the character. The reason is obvious the parser that compares a string to the pattern needs only one operation to match an underscore before it can move to the next character but has to do some look-ahead parsing to resolve a percentage sign. The wildcards can be inserted in the middle or beginning of a pattern. Thus B K will match BOOK BLOCK and BK but it will not match BLOCKS . The parser would scan each letter and classify it as a wildcard match or an exact match. In the case of BLOCKS the initial B would be an exact match and the parser would continue L O and C have to be wildcard matches since they don t appear in the pattern string K cannot be classified until we read the last letter. The last letter is S so the match fails. For example given a column declared to be seven characters long and a like predicate looking for names that start with Mac you would usually write select FROM People WHERE name LIKE Mac However this might actually run faster select FROM People WHERE name LIKE Mac_ OR name LIKE Mac__ OR name LIKE Mac OR name LIKE Mac_ The trailing blanks are also characters that can be matched exactly. Tricks with Patterns 263 Putting a at the front of a pattern is very time-consuming. For example you might try to find all names that end in -son with this query select FROM People WHERE name LIKE son The use of underscores instead will make a real difference in most SQL implementations for this query because most of them always parse from left to right. SELECT FROM .
