Using LIKE...having problems with returned recordset

[Shrum]

How would I structure a Select statement where I am looking for a specific value. Here's the catch:

The value I'm looking for may be the first term, middle term, or last term in a string.

The problem I see is that if I do:

...WHERE `location` LIKE 'Canada' (only if alone ?)
...WHERE `location` LIKE 'Canada%' (only if at start)
...WHERE `location` LIKE '%Canada%' (only if in middle)
...WHERE `location` LIKE '%Canada' (only if on end)

I want a *simple* WHERE statement that is unconditional...if the word 'Canada' is anywhere in the `location` field, return it.

Is this possible?

TIA

 

[vbkris]

...WHERE `location` LIKE '%Canada%' (anywhere in location)




Known is handfull, Unknown is worldfull

 

[Shrum]

Nope. If I do:

...where `location` LIKE '%Canada%'... = no records
...where `location` LIKE 'Canada%'... = no records
...where `location` LIKE '%Canada'... = no records
...where `location` LIKE 'Canada'... = no records

BUT:

...where `location` LIKE 'Toronto, Canada'... = 30 records

but then again that is an EXACT match.

Any other ideas?

 

[vbkris]

well i have used it in MySql and it gives me no problem...

but i will get back to u...

hey it maybe case sensitive also. check on that....

Known is handfull, Unknown is worldfull

 

[vbkris]

try removing this '
...where location LIKE '%Canada%'...

Known is handfull, Unknown is worldfull

 

[Shrum]

Same result without the backticks

FYI: The backticks are standard and documented in the MySQL manual. This is the preferred method, according to the documentation.

 

[vbkris]

can i have ur full query?

Known is handfull, Unknown is worldfull

 

[Shrum]

SELECT * from repository WHERE `location` = 'Canada'

OTOH: I might just break country names out into their own field but I'd still like to figure this issue out.

Thanks for the help.

 

[vbkris]

select name from emp_details where name like '%Moh%'

this query works for me....

the returned result is:
Krishnan Mohan


i think this is what u wanted...

Known is handfull, Unknown is worldfull

 

[Shrum]

try getting the last name like:

'%Mohan%'

I think you'll find that it's picky about the term being at the end (or beginning) and using the wildcard '%' character

Let me know what you get

 

[vbkris]

select name from emp_details where name like '%Mohan%'

this too works !!!! gives the same result...

Known is handfull, Unknown is worldfull

 

[QatQat]

Sorry Shrum
I just read through your query



SELECT * from repository WHERE `location` = 'Canada'

this will not work as you are indeed querying for an exact match; try to use LIKE


SELECT * from repository WHERE `location` LIKE '%Canada%'

This will work.

Bye

Qatqat

Life is what happens when you are making other plans.

 

[vbkris]

alas QatQat if it was so simple...

Known is handfull, Unknown is worldfull

 

[rudof]

WHERE `location` LIKE "%Canada%"

 

[Shrum]

My bad...I meant 'LIKE' (not '=' in my previous post).

Hmmm...I tried a number of SQL statements but for some reason it just never returned the data it *should* of. Might be a bug with the version of MySQL my host is using.

OTOH, I decided it would be better to seperate out the country data into it's own field so that works out; searches for exact matches works properly.

Thanks to everyone that chipped in to help me on this one.

Sean Shrum

http://consulting.shrum.net