On Tue, 20 Jan 2004, Chris Boget wrote:
> > > The data I'm working with looks like this:
> > > table1.columnA = '1;3;4;6;8;9;12;13;14;15';
> > > table2.columnA = '3';
> > > table2.columnB = 'this';
> > > I need to write a query that will do something along these lines:
> > > SELECT * FROM table1, table2 WHERE
> > > table2.columnB = 'this'
> > > AND
> > > table1.columnA LIKE CONCAT( '%', table2.columnA, '%' );
> > AND
> > INSTR(table1.columnA, table2.columnA)
>
> Well, the only problem with going this route is that if table1.columnA 's value
> was, instead, '1,4,6,8,9,13,14,15', that row would still match (when it really
> shouldn't) because of the '13' within the string. The '3' from table2.columnA
> is part of the table1.columnA string.
Oops
INSTR(a, CONCAT(',', b, ',')) OR
INSTR(a, CONCAT(b, ',')) = 1 OR
INSTR(a, CONCAT(',', b)) = CHAR_LENGTH(a) - CHAR_LENGTH(b)
might work better, sorry about that (replace a and b with appropriate
columns).
The first line checks if ,column, exists, which will be in all cases when
it's not either first or last.
The second line checks if it exists first in the commaseparated list, and
the last line checks if it exists last.
There's probably an easier way to do this, but since I submitted a faulty
reply I should atleast make up for it ;)
cheers,
Tobias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
