mysql search problem

Anon

Hi,

I can't find out how to deal with the following pattern match problem.

for example, values for column X:

row1: Harold,John,Mark
row2: Steve,Johnny,Bob
row3: John

How can I set up the query in a way that when I search for "John" in column X, only a match is found on row 1 and 3?

using LIKE "%john%" will also match row 2 which should not happen!

Thanks,
Raoul

saloon12yrd

First of all LIKE is bad, it tends to become very slow on large databases. Check out FULLTEXT indexes. It's not very good either but at least it's an index.
You can do LIKE '%,John,%' if a) you're not handling thousands of datasets and b) you can guarantee the comma seperation.

Dominique

Anon

If you are specifically looking for John, why don't you use
like 'John';

Anon

Thanks,

I thought of that, but look at row 3, no comma seperation!

Anon

Will that match the John in row 1 as well?

row1: Harold,John,Mark

Thanks,
Raoul