Help needed in SQL Query Search!

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help needed in SQL Query Search!

Author

Topic

padsp
Starting Member

21 Posts

Posted - 2005-08-02 : 17:28:24

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

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-08-02 : 18:25:53

Might want to look at this project which uses sql xp_ to perform some of the tasks you are referring to.

http://www.codeproject.com/database/dmetaphone4.asp

If you are looking for a commercial app check out:

http://www.intelligentsearch.com/name_searching/intelligence.html#phonetics

Nathan Skerl

padsp
Starting Member

21 Posts

Posted - 2005-08-03 : 10:41:46

Nathan,

Thank you for your time to read my post and reply to it. let me go thru each link to see which helps me.

Thanks
Bob

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-08-03 : 11:43:03

No problem. Also, check this one out... kinda interesting:

[url]http://www.sqlservercentral.com/columnists/mcoles/soundmatchingandaphonetictoolkit.asp[/url]

Nathan Skerl

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-03 : 11:45:54

why does soundex not work??
have a look at 'Difference' in BOL

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

padsp
Starting Member

21 Posts

Posted - 2005-08-03 : 14:54:14

Nathan,

That link is more interesting. i'm just testing it now. again thanks.

Corey,
I've tried with Soundex like in my query,
select * from employee where soundex(lname) = soundex(searchArg)
no useful results. if you have used soundex before, i would appreciate if you could post examples.

Thanks
Bob

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-03 : 17:00:25

Select * From employee where difference(lname,searchArg) >= 4 --(3 would be looser)

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-08-03 : 17:11:21

Yea, I thought it was an interesting project. But as Cory suggested, looks like soundex can fulfil your requirments. Please report back to us on any performance/accuracy issues you run into.

Nathan Skerl

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-03 : 17:35:07

you could also look at the

This is nice too... although it is a bit more intensive: Levenshtein Edit Distance Algorithm (A better Soundex)

and then there is this, which I actually haven't read before: Better Phonetic Matching Algorithm (A better Soundex)

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

padsp
Starting Member

21 Posts

Posted - 2005-08-03 : 17:43:40

Hi Corey,

Thanks for your time. I have tried the following query,
select * from employee where difference(lname,'jeorge') >= 4
Here 'jeorge' is the search argument.
It brings similar results like name starts with 'J' below,
"Jarchow,Jarecka,Jarosz...Jorge,Juriga"
But what i was expecting name like "George" should be in the result set. Because user doesn't know name starts with "J" or "G". If you have anyother idea, please post it for me.

Thanks
Bob

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-05 : 07:26:30

Declare @myTable table (a varchar(100))
Insert Into @myTable
Select 'George'

Select * From @myTable Where difference(a,'jorge')>=4
Select * From @myTable Where difference(a,'jorge')>=3

You could then use the Levenshtein algorithm to rank your results further...

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources