Jeff Moden (9/24/2012)
emoore 99634 (9/24/2012)
True, but others reading through a forum might be looking for ideas on how to tackle problems like this.
So I will chime in that I too have been handed this same problem many times over the years and it's a nightmare! The permutations of names that can be entered into a single string value is almost impossible to deal with. (The same issue can occur with addresses...oh my!) Any specific solution would be dependent on the business requirements.
The only permanent solution I've found is to parse the name-string into the proper individual columns for prefix, first name, middle name, last name, suffix etc. But this is hard to do for compound names such as 'Van Dyke' or a suffix like 'DDS MD'. For me its always been a laborious iterative process to get the names right. If new data is constantly coming in then this may be difficult to keep up with.
Otherwise, one way to deal with searching or filtering on such bastard data (besides the simple LIKE statement) is to set up a Full-Text index on the column and develop the search criteria to use that. This will likely create a lot of false positives and for any large amount of data the best one can usually do is to offer up a list of the closest matches and then let a user pick from the list. When dealing with fuzzy data humans can still do some things much more reliably than our computers.