SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Match firstname lastname

Match firstname lastname

Steven Willis
Steven Willis
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2017 Visits: 1721
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.


You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum