Calvin Lawson (1/15/2010)
Thanks Ben, great idea for when you absolutely have to have those fast lookups with wildcards at the beginning of the search term. Which happens more often then one might think...
One small point; for the sake of code clarity, I would do something like this:
SELECT NAME FROM TEST_TABLE WHERE NAME_REVERSED LIKE REVERSE('%ID')
True - I suppose it depends on whether you can get the calling code to reverse the search term for you. If you have a high volume web site doing lots of searches for example, you might want to get the web code to do this (albeit minor) string reversion as SQL is probably going to be slower at doing this than a web farm.
On the other hand it makes it better for code isolation purposes if you can just give the search term you are looking for to the SQL code
So another 'it depends' answer then!