Hi everyone,
I'm trying to implement SQL Server database search. The details are:-
1. I have table called EMPLOYEE has FNAME,LNAME etc cols.
2. User might look for any employee using either FNAME or LNAME
3. I have search box in asp.net where user could enter search string
The sample data:
FNAME LNAME
abc george
def george
rkis lita
rose lita
The query i wrote:
SELECT * FROM EMPLOYEE WHERE lname like '%' + searchArg + '%'
My problem is:-
1. let's say user is looking for employee "george"; In search string instead of typing actual word "george", user could type "jeorge"; because the name pronounce or sounds like similar.
Same thing with user could type "leta" instead of "lita". Again these are all similar sounds.
When you look for "jeorge" in GOOGLE; it says "did you mean george"; i would like implement something like that. somewhere i saw SOUNDEX would do what i am looking for; but i no luck for me.
Is this possible anyway in T-SQL or Fulltext search.
Your help is greatly appreciated.
Thanks
Bob
You were on the right track with Soundex, here's how you can use it. Also look into difference.
Sample data: (pubs)
au_id au_lname au_fname
---- ------------ -------
409-56-7008 Bennet Abraham
648-92-1872 Blotchet-Halls Reginald
238-95-7766 Carson Cheryl
722-51-5454 DeFrance Michel
712-45-1867 del Castillo Innes
427-17-2319 Dull Ann
213-46-8915 Green Marjorie
527-72-3246 Greene Morningstar
472-27-2349 Gringlesby Burt
My Queries:
select*from authorswheresoundex(au_lname)=soundex('Benet')
Returns:
au_id au_lname au_fname
---- ------------ -------
409-56-7008 Bennet Abraham
select au_id,au_lname,au_fnamefrom authorswheredifference(au_lname,'whit')> 3
Returns:
au_id au_lname au_fname
---- ------------ -------
172-32-1176 White Johnson
Hope this helps, as you can see here it is definitely possible to get the "similar" functionality. Check books online for more info on difference, iirc it returns one of a number of different values that state how close or different the words are.
Scott
No comments:
Post a Comment