[Resolved] Correct way to query comma-delimited data

[new7media]

What is the best way to query a database when a particular column contains comma-separated data?

Here's an example of the data:

City - North
City - West
City - South
North Suburbs
West Suburbs
South Suburbs

For a particular record, the data could appear in the "location" field like so:

City - North,City - South,Southern Suburbs

My fear is that querying the database with LIKE, for example:

SELECT * FROM table WHERE location LIKE "City - North"

might return both "City - North" and "North Suburbs" since both of these include "North." Is this the case? Is there a better way to query comma-delimited data? Is there a better way to set up the database so that this type of data isn't included?

Also I'm wondering about the spaces in this data. Will these cause problems moving forward?

Appreciate your help!

[mtimdog]

if you use like '%City - North%', it will only find rows that match City - North.

alternatively you could use the IN mysql function

where '$_POST[search]' IN location

[laserlight]

I think that you're better off not using comma-delimited data.
You should use more columns for them instead.

[mtimdog]

Yes, a better way would be to use an associative table linking 2 tables--original and a table for the locations.