Query mySql with LIKE %...% and not pull false records

I have a database that contains two fields that collect multiple values. For instance, one is colors, where one row might be "red, blu开发者_运维问答e, navyblue, lightblue, orange". The other field uses numbers, we'll call it colorID, where one row might be "1, 10, 23, 110, 239."

Now, let's say I want to SELECT * FROM my_table WHERE 'colors' LIKE %blue%; That query will give me all the rows with "blue," but also rows with "navyblue" or "lightblue" that may or may not contain "blue." Likewise, with colorID, a query for WHERE 'colorID' LIKE %1% will pull up a lot more rows than I want.

What's the correct syntax to properly query the database and only return correct results? FWIW, the fields are both set as TEXT (due to the commas). Is there a better way to store the data that would make searching easier and more accurate?

you really should look at changing your db schema. One option would be to create a table that holds colours with an INT as the primary key. You could then create a pivot table to link my_table to colours

 CREATE TABLE `colours` (
`id` INT NOT NULL ,
`colour` VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM 

 CREATE TABLE `mytable_to_colours` (
`mytable_id` INT NOT NULL ,
`colour_id` INT NOT NULL ,
) ENGINE = MYISAM 

so your query could look like this - where '1' is the value of blue (and more likely how you would be referencing it)

   SELECT * 
     FROM my_table 
    JOIN mytable_to_colours ON (my_table.id = mytable_to_colours.mytable_id) 
    WHERE colour_id = '1'

If you want to search in your existing table you can use the following query:

SELECT * 
FROM my_table
WHERE colors LIKE 'blue,%'
    OR colors LIKE '%,blue'
    OR colors LIKE '%,blue,%'
    OR colors = 'blue'

However it is much better than when you create table colors and numbers and create many to many relationships.

EDITED: Just like @seengee has written.

MySQL has a REGEXP function that will allow you to match something like "[^a-z]blue|^blue". But you should really consider not doing it this way at all. A single table containing one row for each color (with multiple rows groupable by a common ID) would be far more scalable.

The standard answer would be to normalize the data by putting a colorSelID (or whatever) in this table, then having another table with two columns, mapping from 'colorSelID' to the individual colorIDs, so your data above would turn into something like:

other colums | colorSelId
other data   | 1

Then in the colors table, you'd have:

colorSelId | ColorId
1          | 1
1          | 10
1          | 23
1          | 110
1          | 239

Then, when you want to find all the items that match colorID 10, you just search on colorID, and join that ColorSelId back to your main table to get all the items with a colorID of 10:

select * 
from 
    main_table join color_table 
on 
    main_table.ColorSelId=color_table.ColorSelId 
where 
    color_table.colorId = 10

Edit: note that this will also probably speed up your searches a lot, at least assuming you index on ColorId in the color table, and ColorSelId in the main table. A search on '%x%' will (almost?) always do a full table scan, whereas this will use the index.

Perhaps this will help to you:

SELECT * FROM table WHERE column REGEXP "[X]"; // where X is a number. returns all rows containg X in your column
SELECT * FROM table WHERE column REGEXP "^[X]"; // where X is a number. returns all rows containg X as first number in your column

Good luck!

None of the solutions suggested so far seem likely to work, assuming I understand your question. Short of splitting the comma-delimited string into a table and joining, you can do this (using 'blue' as an example):

WHERE ', ' + myTable.ValueList + ',' LIKE '%, blue,%'

If you aren't meticulous about spaces after commas, you would need to replace spaces in ValueList with empty strings as part of this code (and remove the space in ', ').