Middle Search Support

Some of the databases that I inherited contain search that are based on finding a string anywhere within a last name such as:


WHERE lastName like '%smith%'

It is desired that each of these names be returned:


Smith
Smithson
Nesmith

What is presently done is that updates to the last name fields trigger that substrings of the last name be sent off into a substring table wtih retention of no 2-char substrings. For these three last names the following would be kept:


(1) Smith, (2) mith, (3) ith and (4) th
(1) Smithson, (2) mithson, (3) ithson, ..., (n) on
(1) Nesmith, (2) esmith, (3) smith, ... (n) th

The where now becomes


WHERE lastNameSub like 'smith%'

This seems to make the search routine by last name faster. I would like to improve on this if I can. Suggestions?


Dave