July 13, 2010 at 9:05 pm
Hard to explain, trying to do a select query that return anything similar to specific key word.
Example let say I run a query to display all customer name that contains name like '%SCOTTIE%' or '%AMERICANA%', the current like '%%' doesn't work, wondering if I have other options. TIA
data should return all values
SCOTT
SCOTTI
return
AMERICAN
AMERICO
July 13, 2010 at 9:12 pm
Figured it out.. use SOUNDEX right?
July 13, 2010 at 10:28 pm
John N (7/13/2010)
Figured it out.. use SOUNDEX right?
I'm pretty sure I wouldn't use SOUNDEX... it's a little too "fuzzy" for my taste.
I haven't had to use it myself but it seems that fulltext indexing may be what you're looking for. Take a look for it in the index of Books Online.
--Jeff Moden
July 13, 2010 at 11:26 pm
post your query
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
July 14, 2010 at 6:20 am
The soundex query? select custname from mastercustomer where soundex(custname) = soundex=('SCOTTIE').
result:
custname
scott
scotty
scot
....
This is what i'm looking for but is this the right contxt to use soundex? or should i use full-text indexing like Jeff suggested?
July 14, 2010 at 6:36 am
John N (7/14/2010)
The soundex query? select custname from mastercustomer where soundex(custname) = soundex=('SCOTTIE').result:
custname
scott
scotty
scot
....
This is what i'm looking for but is this the right contxt to use soundex? or should i use full-text indexing like Jeff suggested?
Soundex is bad. An example I've come across several times, and once very recently, is as follows.
SELECT Soundex('SMITHWICK'),
Soundex('SIMONS')
SELECT Difference('SIMONS', 'SMITHWICK'),
Difference('SMITHWICK', 'SIMONS')
July 14, 2010 at 7:25 am
Jeff Moden (7/13/2010)
John N (7/13/2010)
Figured it out.. use SOUNDEX right?I'm pretty sure I wouldn't use SOUNDEX... it's a little too "fuzzy" for my taste.
Or not "fuzzy" enough
SELECT Soundex('philip'),
Soundex('filipe')
July 14, 2010 at 8:01 am
The most closest thing to a "Fuzzy" search in SQL is using Full-text predicates (google FREETEXTABALE & CONTAINSTABLE)
You might not like using it...
If you want to have good fuzzy search off-shelf solution, have a look (It has a demo):
You can try to implement different fussy search algorithms (eg. Jaro Winkler for example) yourself. It is no going to be easy, defenetly not a beginner task. You can do it as T-SQL UDF, but it will be very slow. CLR will work much better, but still it is not going to be fast for large data sets.
This one can help you a lot on your way to fuzzy search:
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply