Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
VA3018
Starting Member
3 Posts |
 Posted - 2012-06-19 : 10:15:00
|
| I have a problem that I'm not sure how to resolve. I'm creating a member list module for our organization's web site. This is the scenario:- We are using a CMS, so structural changes to content database objects can't be made.- Member table includes the fields UserName, FirstName, LastName- I created a stored procedure to insert the fields UserName and FullName (LastName + ', ' + FirstName) into a temporary table. - The stored procedure has a search parameter, @SearchInput. The stored procedure WHERE statement is: (u.UserName LIKE '%' + @SearchInput + '%') OR (u.FullName LIKE '%' + @SearchInput + '%') (I can concatenate the FirstName and LastName fields somewhere else if necessary and insert FirstName/LastName into the temp table.)The problem is, when a user enters 'John Doe,' the member isn't found, and wouldn't be in any of the three fields. I'm not familiar enough with writing functions, etc. to parse out the parameter string and compare it with all three fields. Can someone point me in the right direction for an approach to take to resolve this issue?Thanks! |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-06-19 : 11:48:28
|
| Name searches are a bit tricky. One question though:>>when a user enters 'John Doe,' the member isn't foundIs it not found because of the comma? I wouldn't expect it should be found of they include a character that isn't part of the name.Rather than a single @searchInput parameter can you provide @firstName and @lastName parameters? That would help for when the user only knows the first name and it happens to be a two part name like "Mary Beth" for example. Hard to know programmatically if that is a first name only or firstname and lastname.That won't help when the user provides a middle initial, prefix, or suffix which isn't stored in your firstname and lastname columns. If you provide specific rules for how the search should work from a business perspective we can help with the code.I recall there have been other name search topics posted so you should search and look through those too.EDIT:oh yeah - some users will expect nick-names to work too. ie: why doesn't your search find Bobby Fisher - when it is stored as Bob or Robert.Be One with the OptimizerTG |
 |
|
|
VA3018
Starting Member
3 Posts |
Posted - 2012-06-19 : 12:49:18
|
| DonAtWork - Thanks for the link. I've been looking at the fnParseString but don't quite understand how it will parse @SearchInput but I'm playing with it. TG - To answer your question, yes. If I were to enter 'Doe, John' as the @SearchInput parameter, the record would be found. You make very valid points - possible middle initial, punctuation, nicknames, which is why I'm so perplexed and was thinking about the possibility of parsing the input string. Unfortunately, both of our technical leads are out this week (who requested this capability) so I can't discuss it with them until Monday. I agree that rules need to be created. I'll let you know how this plays out.Thanks so much for your responses. I appreciate the help! |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2012-06-19 : 14:05:39
|
If they expect it to find John, Johnny, Jon or Bob, Robert, Bobby, you get to play with Fuzzy logic and Soundex!  How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
VA3018
Starting Member
3 Posts |
Posted - 2012-06-19 : 14:23:17
|
| Oh boy! That sounds like fun. I think I'll google it. :) Thanks! |
|
|
|
|
|
|
|
|
Need an approach