RegEx in select from table in db

Question

Problem:

I have a table like this :

A | B
-----------------------------------------
1 | 5,25,24,22,21,6,19,18,17,15,13,11
2 | 25,15,17,4,33,12,34,40,24,5,1,26,43,9
3 | 25,15,11,36,29

I need to select rows where my number is in column B.

try:

I use RegEx for this. I write this code :

SELECT *
FROM `table`
WHERE `B` REGEXP "([^\d]|^)MYNUMBER[^\d]

and RLIKE

([^\d]|^)MYNUMBER[^\d] : ([^\d]|^) : not number or be a first char. MYNUMBER [^\d] : not number

When MYNUMBER is 25 or 34 or any double digit number, I haven't got any problem. But when MYNUMBER is 5 or 1 or any one digit number, it's trouble.

Result example1:

input: MYNUMBER : 24

result:

A | B
-----------------------------------------
1 | 5,25,24,22,21,6,19,18,17,15,13,11

It's okay.

Result example2:

input: MYNUMBER : 5

result:

A | B
-----------------------------------------
1 | 5,25,24,22,21,6,19,18,17,15,13,11
2 | 25,15,17,4,33,12,34,40,24,5,1,26,43,9
3 | 25,15,11,36,29

Wrong answer, row 3 is wrong.

It is strange:

I tried my expression in regexr.com and it's true.

Have you looked at find_in_set()? dev.mysql.com/doc/refman/5.0/en/… — Andrew
– Andrew, Commented Dec 11, 2014 at 23:30
FIND_IN_SET(), or add commas to the beginning and end of the string you're searching, and what you're searching for. e.g. search for string ,5, within CONCAT(',',t.B,',') — spencer7593
– spencer7593, Commented Dec 11, 2014 at 23:38
I recommend you check out chapter 2 of Bill Karwin's excellent book 'SQL Antipatterns' http://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers/dp/1934356557 — spencer7593
– spencer7593, Commented Dec 11, 2014 at 23:44

Hartmut Holzgraefe · Accepted Answer · 2014-12-11 23:49:59Z

1

MySQL has a function for this: FIND_IN_SET()

http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_find-in-set

In the long run you may want to think about normalizing this into two tables ...

edited Dec 11, 2014 at 23:49

answered Dec 11, 2014 at 23:29

Hartmut Holzgraefe

2,76515 silver badges15 bronze badges

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

Comments

RobP · Accepted Answer · 2014-12-11 23:43:36Z

1

The number has to be a consecutive set of digits that is either surrounded by commas, or is the first or last thing in the string. You could add to this to tolerate optional whitespace if desired.

SELECT *
FROM `table`
WHERE `B` REGEXP "^25," OR `B` REGEXP ",25," OR `B` REGEXP ",25$"

edited Dec 11, 2014 at 23:43

answered Dec 11, 2014 at 23:30

RobP

9,5923 gold badges22 silver badges33 bronze badges

5 Comments

RobP Over a year ago

@spencer7593 whoops yes, logic was wrong and had false-positive matches. And I am not sure why this answer would be used rather than FIND_IN_SET except as an academic exercise, but hey, maybe as a REGEXP learning exercise.

spencer7593 Over a year ago

This a workable approach. Sometimes the MySQL optimizer doesn't handle OR conditions that well. Consider the equivalent: CONCAT(',',B,',') REGEXP ',25,'.

RobP Over a year ago

I'd have to test to see which was faster against various engines. I find the OR version more intuitively readable and checkable for correctness, but the CONCAT with a single REGEXP is elegant.

spencer7593 Over a year ago

,,, in this particular case, either way, MySQL can't make use of a range scan operation. It's going to have to evaluate the predicate for every flipping row in the table. So, we're basically weighing the CPU/memory cost of evaluating three regular expressions (in the worst case of no matches found), against the cost of allocating/copying a string, and doing a single regular expression evaluation. My bet is the difference is negligible. (I don't think the FIND_IN_SET is going to give much different performance either.)

RobP Over a year ago

Agreed. Which is why normalizing into two tables is a far, far better answer than any of these!

Collectives™ on Stack Overflow

RegEx in select from table in db

Problem:

try:

Result example1:

Result example2:

It is strange:

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Problem:

try:

Result example1:

Result example2:

It is strange:

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related