How do i use LIKE for a search query?

[OuTCasT]

I cant remember how to do the search with LIKE...

Ive got a textbox where the user will search for a company name
now i want them to be able to type in anything and it must bring back the companies with that text in.....

???

[Delerna]

where field like 'str%' --returns records where field begins with str
where field like '%str%' --returns records where field contains str
where field like '%str' --returns records where field ends with str

[OuTCasT]

where field like 'str%' --returns records where field begins with str
where field like '%str%' --returns records where field contains str
where field like '%str' --returns records where field ends with str

I want to be able to type in anything.

For instance if there was a company called WebSol-IT in the db
and i typed in Web or Sol or IT in the textbox then it would bring that back WebSol-IT back

[code green]

and i typed in Web or Sol or IT in the textbox then it would bring that back WebSol-IT back

Then Delernas LIKE %str% will work.
Or are you trying to do Google type search - typing "Web OR Sol OR IT" in the box?

[OuTCasT]

Then Delernas LIKE %str% will work.
Or are you trying to do Google type search - typing "Web OR Sol OR IT" in the box?

its like a company will book in a item to get repaired, they come back 2 days later and tell the guy they want to pick up there item, he asks them there companyname and he types it in and it will search for that company, so even if he spelt it wrong, or put a space between the words even though there isnt it would still bring back something in that liking.

[code green]

so even if he spelt it wrong, or put a space between the words even though there isnt it would still bring back something in that liking.

This is tricky.
I suppose it could be done in SQL but I would prefer using code.
If it was spelt wrong you would need a lookup table for common mispellings or
break up the word into chunks and look for partial matches.
Spaces again could be broken into chunks to look for each seperated word or the whole word with spaces removed.
I wrote a similar bespoke program a couple of years ago.
I shudder to think how un-sophisticated that probably looks now

[ck9663]

That'll be a search engine/fuzzy matching algorithm. If you can write that efficiently, you can get rich coz that's how most search engine works. You can replace google ;)

-- CK

[Delerna]

I think you could probably handle spaces by replacing them with %.
That way the entry "Col Turner Industries" would look like

where field ='%Col%Turner%I ndustries%'
I'll have to try that one day.

Or maybe you could use the replace function on both sides of the where equation to replace spaces with an empty string. Can't remember the exact syntax
something like
where replace(field,' ','') = '%' + replace(@param. ' ','') + '%'




Handling Mis-spellings
Yea....good luck with that :)
As CK said, You could replace Google
Actually, if I knew the efficient answer I would keep it to myself