For reference,
WHERE 9 IN (REPLACE(geo_region,'|',','))
did not work because IN expects a list of columns, each of whose values is to be compared to 9. You put 1 column in the list. Had you compared the string '9' this way, you would have gotten only those rows where geo_region='9'. Because you compared to the integer 9, however, mysql automatically tried to convert geo_region to an integer as well. This conversion reads the string left to right, stopping at the first non-numeric character, a comma (formerly a |), in your case. Hence, rows which start with '9' and have a | or nothing as the second character would match.
The best solution, as we've all agreed, would be to redesign the table as Rhino suggested. Since you can't do that, there are some alternatives. Stephen has already suggested one:
WHERE CONCAT('|',geo_region,'|') LIKE '%|9|%'Another would be:
WHERE CONCAT('|',geo_region,'|') RLIKE '|9|'A third would be:
WHERE FIND_IN_SET('9', REPLACE(geo_region,'|',','))I think that's the one your were trying for. I don't know if any of those would be faster than the others -- you'd have to test them.
Note that none of these can use an index on geo_region, however, because we are comparing to the value of a function of geo_region rather than to the (indexed) values of geo_region. Mysql will have no choice but to perform a full-table scan, calculating and comparing for each and every row. With a small table and a lightly used db that may be no big deal, but it won't scale. That is the real disadvantage of cramming multiple values in one cell.
Michael
Andrew Dixon wrote:
Hi.
Thanks Stephen, the works like a dream... Unlike this database which is more like a nightmare!!!
Best Regards
Andrew.
-----Original Message-----
From: Stephen E. Bacher [mailto:[EMAIL PROTECTED] Sent: 03 August 2004 12:16
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: In Statement Help
Andrew Dixon <[EMAIL PROTECTED]> wrote:
Hi Everyone.
I require some help with an IN statement I'm trying to get working. I have inherited a database from someone else in which there is a table with project information and a field containing which geographic regions the projects relate. As a single project can relate to multiple geographic regions the person who created the database create this field as a varchar field and populated it with a pipe (|) delimited list of ID's of the geographic regions (ID from another table). An example of the data in this field is:
1|5|9|10|12
Or
1
Or
9|5|7
I have been asked to write a query to return all the projects for a single geographic region. This is what I have come up with:
SELECT project_title FROM projects
WHERE 9 IN (REPLACE(geo_region,'|',','))
ORDER BY project
[...]
I would suggest something like
WHERE CONCAT('|',geo_region,'|') LIKE '%|9|%'
- seb
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
