July 17, 2009 at 3:22 pm
I have a table Contacts as described below
CREATE TABLE dbo.Contacts
(
ContactID int identity primary key,
FirstName varchar(30),
LastName varchar(30),
Address varchar(100),
City varchar(50),
State char(2),
Zip varchar(10),
Phone varchar(12),
Email varchar(50)
)
I want my users to be able to search contacts using firstname or lastname or complete name.
It is trivial to search only on firstname or lastname
[p]
set @sql = 'select * from contacts where '
set @sql = @sql + 'Contact.LastName like ''%' + @srchText + '%'' OR Contact.FirstName like ''%' + @srchText + '%'''
exec (@sqlstmt)
[/p]
Any suggestions on the best way to be able to search when users specify the contacts complete name.
July 20, 2009 at 2:35 pm
I have a concern from looking at your "trivial" example. If your user enters both a first name of "John" and a last name of "Smith", the code displayed will show all names that have a first name of "John" AND all names that have a last name of "Smith". It will also probably do a table scan, because the OR condition will keep it from relying solely on a firstName index or a lastName index.
You would do better to test if both first name and last name have been entered. Pseudo-code follows:
IF @firstName > '' and @lastName = '' Select ... where Contact.FirstName like @firstName+'%'
IF @firstName = '' and @lastName > '' Select ... where Contact.LastName like @lastName+'%'
IF @firstName > '' and @lastName > '' Select ... where Contact.LastName like @lastName+'%' AND Contact.FirstName like @firstName+'%'
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 20, 2009 at 2:49 pm
Upon re-reading, I see that you have the user entering a single search string. I think it would be better to have them enter a first name and a last name string separately. To take advantage of any firstname or lastname indexing, you have to parse the string entered into the one or more substrings that are separated by space, comma, or period. (You may want to do even more cleanup.) Then build your query based on those strings. Can you assume that the users will enter firstname then lastname? If so:
User enters "John Smith" so you parse to @firstname = "John" and @lastName = "Smith".
However, this leads to lots of complications:
John Thomas Smith
John Smith Jr
John Smith Esq
Jane Jones Smith
Ms. Jane Jones
Better to have them specify a single first name and a single last name. Check for and remove any commas, periods, and known suffixes. Then your queries become simple (and faster).
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 20, 2009 at 4:51 pm
Thank you for your reply.
It is a web based application and our users have always wanted the feature of searching their contact listing based on first name, last name or complete name. In the solution that I am testing for performance, The web application strips all special characters and whitespace from the search text and passes the search string to the stored procedure.
In the stored proc, I check for the following matches.
firstname = search text or lastname = search text or (firstname+lastname = searchtext) or (lastname+firstname = searchtext).
'Contact.Last_Name like ''%' + @srchText + '%'' OR Contact.First_Name like ''%' + @srchText + '%''' +
' OR (Contact.First_name' + '+' + 'contact.last_name) like''%' + @srchText + '%''' +
' OR (Contact.last_name' + '+' + 'contact.First_name) like''%' + @srchText + '%'''
I did not notice performance degradation with this query on a table with 200,000 records.
Do you know of any others way to improve the query?
Thanks
July 20, 2009 at 5:56 pm
Can I search for "O'Neil"?
Can I search "';drop table dbo.Contacts;"?
Have you thought about full text index?
July 20, 2009 at 6:26 pm
In its current implementation, we cannot search for names with '. Thank you for pointing this. I will start looking into full text index.
as all the spaces and special chacracters are removed from the searchtext, it is free from sql injection. The search string ;drop table dbo.Contacts;" becomes droptabledbocontacts
Thanks,
July 21, 2009 at 8:44 am
In our web application we can search for a User by a portion of the name or logonid as follows
SELECT UserSID
,LoginID
,NameLast
,NameFirst
,NameMiddle
,NameTitle
,Phone
,Location
FROM dbo.User
WHEREUser.NameLast LIKE CASE
WHEN LEN(@NameLast) > 0 THEN @NameLast + '%'
ELSE User.NameLast END AND
(User.NameFirst LIKE CASE
WHEN LEN(@NameFirst) > 0 THEN @NameFirst + '%'
ELSE User.NameFirst END OR (User.NameFirst IS NULL))
AND
(User.NameMiddle LIKE CASE
WHEN LEN(@NameMiddle) > 0 THEN @NameMiddle + '%'
ELSE User.NameMiddle END OR (User.NameMiddle IS NULL ) )AND
User.LoginID LIKE CASE
WHEN LEN(@LoginID) > 0 THEN @LoginID
ELSE User.LoginID END
ORDER BY NameLast, NameFirst, NameMiddle, NameTitle
The search options @namelast, @namefirst, etc are all optional and independently searched.
Terri
To speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]
July 21, 2009 at 8:56 am
ajaykshanker,
Nor will you be able to search for names with spaces.
What about diacritics? e.g. â, ã, ä, å, æ
Regarding SQL injection, what about carriage returns/line feeds/tabs?
July 21, 2009 at 9:52 am
In the stored proc, I check for the following matches.
firstname = search text or lastname = search text or (firstname+lastname = searchtext) or (lastname+firstname = searchtext).
I did not notice performance degradation with this query on a table with 200,000 records.
Again, the or searches are almost certainly force a table scan which is less efficient than the use of an index, even if the entire table can be scanned in less than a second. How did you measure the performance?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 22, 2009 at 11:56 am
Terri, Thanks a lot for the code sample. I updated the query based on your sample. It is working great and I dont have to worry about SQL injection. Thank you again.
July 22, 2009 at 1:02 pm
ajaykshanker (7/22/2009)
Terri, Thanks a lot for the code sample. I updated the query based on your sample. It is working great and I dont have to worry about SQL injection. Thank you again.
Glad it helped. Thanks for the feedback.
Terri
To speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply