Howdy all.

I've been re-thinking traditional methods of storing names in tables. Typical "full" tables have the usual Prefix, First, Middle, Last, Suffix columns defined - and applications typically present these fields for independent entry.

But, at least for the application I'm designing now - I can't think of any report I'll be doing that requires filtering by first or last names. So to me, storing them in this fashion only increases complexity for data entry and presentation. I should just use a simple "FullName" column and let it go.

The challenge is searching. Users should be able to type "Smi" and have everything from "Smiley Robinson" to "John Smith" to "Adam Smith Enterprises" show up. But how to accomplish that with a relatively speedy search? A standard indexed search would not work here.

So two alternatives come to mind. One, a UDF that breaks a name up into components based on whitespace and punctuation and is used for index generation. The other is a stored procedure that does the same processing during inserts and updates - but stores the components in a secondary table which could be indexed for searching.

Any thoughts on this? Anybody already done something similar?