query like or something similiar

Question

Post reply

query like or something similiar

John N

SSC Veteran

Points: 235
More actions
July 13, 2010 at 9:05 pm

#235108

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John N SSC Veteran Points: 235 More actions · Answer 1

John N

SSC Veteran

Points: 235

July 13, 2010 at 9:12 pm

#1192491

Figured it out.. use SOUNDEX right?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

post your query

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

John N SSC Veteran Points: 235 More actions · Answer 4

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?

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

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')

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 6

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')

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 7

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):

www.matchlogics.com

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:

http://anastasiosyal.com/archive/2009/01/11/18.aspx

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]