More SQL.....

[AcIdBuRnZ]

Ok,

Now I have my query problems resolved, here's another one for you lot.....

How can you perform 'fuzzy' searches?

What I am really looking for is something like this:

If you search for jumper, it will return all records containing words like jumpers, jumping, jump etc

Just so when people search they get more results back, rather than exactly what they typed in.

As you can tell my SQL is a little rusty (and pretty basic) so just need a little guidance here.

Thanks
____________________
Past: Honda NSR125R >> Kawasaki ER-5a2 >> Kawasaki ZZR-600e1 >> Suzuki GSXR-750Y >> Honda VTR 1000 Firestorm
"Chickity China the Chinese Chicken..."

[BigChris]

use wildcards in the select statement when doing the query:

select * from sales_table where query = "*jumper*";

My SQL is rusty too, but wildcards can be used. Just cant remember if its a * or a &

[Kickstart]

Hi

"%" is the normal wildcard for multiple characters. "*" is the one used by the Microsoft attempts at SQL.

All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing

[Suzuki]

Wildcards wont allow you to search for "jumper" and find "jumping" though.

The answer to this is to have a 'fuzzy' table. ie, a row in the table which maps words onto similar search words.

So, 'jumper' would map to the collection of words, 'jumper', 'jump', 'jumping', etc.

Then you perform your search with all of the associated words.

If you're really clever, you'll get your fuzzy table to learn new words as people search for them. That's quite tough though.
____________________
<Simple> no I'm shaven Jon
<Simple> it is a big enough hole.. I'll leave it now
Ride: 1999 Suzuki GSXR600 (yellow/black) IRC: Stats - Relationship Map

[AcIdBuRnZ]

Interesting.

Thanks for replies guys.

Will try using the wildcards. But, TBH, I think rather than allowing users to enter a search string, I'll just give drop down menus so they can select categories, and display results that way.

Much easier to code, and user can see exactly what wil be displayed

Cheers,

Mark
____________________
Past: Honda NSR125R >> Kawasaki ER-5a2 >> Kawasaki ZZR-600e1 >> Suzuki GSXR-750Y >> Honda VTR 1000 Firestorm
"Chickity China the Chinese Chicken..."

[ali-b]

You can do a dictionary search for the root word (so jumper would yield jump) and then do a :-

'select * from my_table where my_column like '%my_rootword%'

The first % is, I guess, optional and you'd get faster results if you didn't use it, but as you used the word jumper it would get results back like 'polo neck jumper' with that syntax

You can use % in MS SQL, it helps to stick to standards to aid portability. Most new implementations use both (because they were dumb and tried to change the standard early on, some people moaned and some people used it so now they have to use both for compatibility)

HTH

A.