MySQL Select using REGEXP

Question

I have following values in my mysql table column named color: 1;2;3;11;12

id | color
1  | 1;2
2  | 2;11
3  | 1;3
4  | 12

I want to use REGEXP to select only those rows from the table which have color 1.

When i use simple expression

color REGEXP '1'

it also selects rows which have values 11 or 12 whereas i am looking for those with value 1 only. So when i use above expression it show me all 4 rows as shown in the example above where as i want it to show me rows with id 1 and 3 only.

Your database is violates the first normal form. You are saving multiple values in one column (color). Please normalize your database. — Jacob
– Jacob, Commented Aug 10, 2011 at 7:27

Conspicuous Compiler · Accepted Answer · 2011-08-10 07:33:34Z

1

You need to use word-delimiters on either side of the 1 to isolate it:

color REGEXP '[[:<:]]1[[:>:]]'

answered Aug 10, 2011 at 7:33

Conspicuous Compiler

6,4901 gold badge44 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

om-nom-nom · Accepted Answer · 2011-10-11 23:01:38Z

0

You could try something like

REGEXP '^1;|;1;|;1$'

If I'm not wrong this will either find a 1 at the start followed by a semicolon or a 1 between two semicola, or a single 1 after a semicolon. This should pretty much be it.

edited Oct 11, 2011 at 23:01

om-nom-nom

63k13 gold badges186 silver badges231 bronze badges

answered Aug 10, 2011 at 7:32

Christoph Grimmer

4,3434 gold badges43 silver badges66 bronze badges

Collectives™ on Stack Overflow

MySQL Select using REGEXP

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related