CODEWITHSUNDEEP
mysqlregex
Saad Bashir
Saad Bashir

Reputation: 4519

MySQL Select using REGEXP

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.

Upvotes: 1

Views: 505

Answers (2)

Christoph Grimmer
Christoph Grimmer

Reputation: 4313

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.

Upvotes: 0

Conspicuous Compiler
Conspicuous Compiler

Reputation: 6469

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

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

Upvotes: 1

Related Questions