Search multiple values within one field without using like?

[hannibal]

I am using MySQL Server5.0.27 and want to perform a select on multiple locations from a multple selection box.

 

For example,

 

SELECT * FROM USERS WHERE Desired_Location='SW' OR Desired_Location='SE' OR Desired_Location='NW'

 

Now, the Desired_Location field in USERS is one column but could have multiple values, as a user may desire to live in may locations.

 

The way I ensivage using the system would be:

 

Desired_Location column for USER 1 contains 'SW','NW'.

Desired_Location column for USER 2 contains 'NW'.

Desired_Location column for USER 3 contains 'SW','NW'.

Desired_Location column for USER 4 contains 'SW','NW','NE'.

 

The below query would not pick up any user exept USER 2:

 

SELECT * FROM USERS WHERE Desired_Location='SW' OR Desired_Location='SE' OR Desired_Location='NW'.

 

Unless I use the next query:

 

SELECT * FROM USERS WHERE Desired_Location LIKE '%SW%' OR Desired_Location LIKE '%SE%' OR Desired_Location LIKE '%NW%'.

 

Is there another method I could use or is this my only choice?

 

If I have a 3 records with 'SW,'SW East','SW West, then a search for LIKE '%SW%' would return all three records, not just the one SW, as SW East and west are "LIKE" SW.

 

I have had a look at:

 

SELECT * FROM USESRS WHERE Desired_Location IN ('SW','NW')

 

But htat explicitly matched the field and only return if the Desired_Location field was = to SW OR NW (i.e. Did not contain more than one location).

 

Hope my explanation is not too complicated, I didn't mean to confuse things any further!!

 

Thanks,

 

Jim.

[rajivgonsalves]

you can use various string function like INSTR etc to determine the same... one thing I am curious about is why you do not want to use like ?

[hannibal]

Only because unless I store the value in the location field as:

 

USER 1 Desired_Location='SW','NE','SW East','NE'

USER 2 Desired_Location='SW East','NE'

USER 3 Desired_Location='SW East','NE'

 

if I put LIKE %SW% in my select statement, SQL will return 3 records, not the 1, as SW East is LIKE SW.

 

I have probably over confused the matter, just seeing if there was another way of using multiple values in one column.

 

For eaxmple, I thought that "IN" could pick up SW out of the date: 'SW','NE','SW East','NE', but it doesn't.

 

Thanks for your promp reply anyway.

[rajivgonsalves]

why don't you just put %'SW'% ofcourse escape the single quote...

[hannibal]

* I meant to say data (not date) above. [edit]

 

That is what I am having to do - Insert the escaped quotes.

 

Like I say, I just thought that there was another way around having to query multiple values within one field. Its not pretty at the best of times

 

Thanks again for your suggestion.

 

Jim.

[fenway]

Like I say, I just thought that there was another way around having to query multiple values within one field. Its not pretty at the best of times

Don't do this... now you see why ti's bad.

[hannibal]

fenway, Do you have an alternative suggestion?

 

Thanks.

[GingerRobot]

Normalize your database. If people will set multiple locations, you should have a separate table. See here for an introduction to database normalization.

[beebum]

You can always use Full-Text.