| Author |
Topic |
|
g_r_a_robinson
Starting Member
45 Posts |
 Posted - 2004-02-25 : 19:00:16
|
| My boss had asked me to provide a search option for our app. This search will simply find job titles. But what he's asked for his a search on job titles wherin you can provide only part of the title. In other words if if wanted to find the job "Clean the cupboards. I could enter cupboards and it would retreive it. I'm extremely new to all of this and I know theres probably a few ways to do this. Can any of theexperts tell me out there where i should start looking or could it be done with some basic tsql.Thanks |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-02-25 : 19:07:44
|
| Look at the LIKE statement and for more advances options Full Text IndexingDavidM"SQL-3 is an abomination.." |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-02-25 : 19:14:48
|
| Also look at SOUNDEX I love using that one for searching, because I can't spell at all!Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-02-25 : 20:11:11
|
| Wow, never heared of SOUNDEX before...looked it up in BOL, but after trying a few options I couldn't find any searches that worked. I might me totally off here but these are some of the methods I tried ('henning' is a valid name in the table users):select * from users where name = SOUNDEX('henning')select * from users where name LIKE SOUNDEX('%henning%')select * from users where SOUNDEX(name) = 'henning'select * from users where SOUNDEX(name) LIKE '%henning%'--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-25 : 20:20:05
|
| WHERE SOUNDEX(name) = SOUNDEX('henning')Tara |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-02-25 : 20:48:26
|
| Cool! But is it possible to use it with LIKE in some way? I use LIKE alot and it would be truly excellent if that would work...i.e. if I searched for Surname LIKE SOUNDEX('%rman%') I would get Herman, Sherman, Henman, Armad, Loman...is this possible?--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-02-25 : 23:02:06
|
| No. Soundex only works correctly with single words, not with word forms or patterns. It creates a code that describes the phonetic pronunciation of a word. This code can be compared to another Soundex code by using the Difference function:SELECT DIFFERENCE('smithers', 'smothers')The Difference value determines how close the words are phonetically, and you can write your query to look for matches within a certain Difference range. See Books Online for more info on how Difference and Soundex work.For the kind of matching you're looking for, full-text indexing is probably your best bet. It won't handle sound-alike matches, but it can handle inflectional word forms and more advanced pattern and word matching. It's also the only way to properly match single words within a sentence or paragraph. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-02-25 : 23:07:21
|
| Ugh, full-text indexing is just another huge can of worms at the moment....I gotta invent some 36-hour days soon...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-02-25 : 23:34:08
|
| Lumbago,FTI sucks the biggest one I know! Well not really, but it needs improvement. The search results are good but performance!It's biggest problem is that it does not restrict its search to part of the Index but all of it.eg. Say you have a million row table and you restrict the search on a range of Keys (say between 1 AND 10) and also have a CONTAINS predicate. The FTI will SCAN all 1 million rows instead of the 10!This is our biggest headache at the moment plus the fact that the SearchCriteria cannot be a column but must be a string.... Imagine having to do 2000+ passes over a million row FTI table and you have our pain....We are going down the denormalised path for this...We add a special prefix with the Key to the FTI column and include the Prefixed key in the search expression. It still searches all 1 million but A LOT faster.....I heard Yukon has improved this.. (Crosses fingers)DavidM"SQL-3 is an abomination.." |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-02-25 : 23:54:39
|
| Thanx for the info David, I feel your pain :) Luckily I don't need it for the website I'm working with and my production DB is only about 200MB so I really don't worry too much at the moment. A confession: Even though using forums like these are an invaluable resource to a developer/dontwannabe-DBA like myself it also makes you painfully aware of all the stuff you don't know but really need to know. There are just so many things to know about and I just can't make time for it all...I'll probably be a kindergardenteacher or something in a year or two ;)--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
|
I need some advice on search design.