MySQL: find a match with numeric pattern

Question

In MySQL, I am trying to find rows with values matching the pattern. In my data, the pattern that I am using is 401.%, 402.%, 403.%, 404.% and 405.%. In other words, I am trying to find any floating number between 401 and 405. In addition, these number is stored as varchar. It is a string. So far, I have tried the following code, but in vain:

select * from my_table where my_column like '401.%'

and so forth.

Could you think of a way to make this work? Thanks for your help in advance.

So how should the LIKE operator treat [1-5]? It searches for this exact string and since you don't have one - it fails to return anything. — zerkms
– zerkms, Commented Mar 26, 2014 at 2:50
What you have there will only work with my_column REGEXP '^40[1-5]\.' — Ja͢ck
– Ja͢ck, Commented Mar 26, 2014 at 3:10
I was not clear about the intention by posting my example code (edited). I was looking for something like regular expression. I wanted to avoid repeating the like statement five times. — midtownguru
– midtownguru, Commented Mar 26, 2014 at 15:09

Eugen Rieck · Accepted Answer · 2014-03-26 02:52:44Z

2

select * from my_table where my_column like '401.%' or my_column like '402.%' or my_column like '403.%' or my_column like '404.%' or my_column like '405.%'

Should perfrom best by far ... sort of loop unrolling in SQL

answered Mar 26, 2014 at 2:52

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

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

3 Comments

Gordon Linoff Over a year ago

Do you actually know if this query will use an index?

Eugen Rieck Over a year ago

Just tried a quick&dirty on my localhost (Ubuntu 12.04, MySQL 5.5.35-0ubuntu0.12.04.2) and execution plan says "possible keys" but not "key" ... but only 1000 rows and all in cache.

Ja͢ck Over a year ago

@GordonLinoff Yes, it will use index; just tested with >= 300k records on 5.5 :)

Gordon Linoff · Accepted Answer · 2014-03-26 02:58:05Z

2

MySQL is very nice about converting numbers to string. So, you can try something like this:

where my_column + 0 >= 401 and my_column < 406

Of course, this doesn't handle situations where bad characters occur after the first three characters. But it might suffice for your purposes.

EDIT:

If you actually wanted to use an index for this, then do:

where my_column >= '401' and my_column < '406' and
      my_column + 0 >= 401 and my_column < 406

The first two string comparisons should take advantage of an index.

edited Mar 26, 2014 at 2:58

answered Mar 26, 2014 at 2:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

5 Comments

Eugen Rieck Over a year ago

as much as I hate disagreeing with somebody of your reputation, but have you thought about waht this means? Casting ALL records to numbers - even those which will not match and forfeiting all index use?

Gordon Linoff Over a year ago

@EugenRieck . . . Possibly a good point, but I'm not so sure that MySQL will use an index for a string of like comparisons connected by or.

Eugen Rieck Over a year ago

If the LIKE does NOT start with a wildcard, the index will be used (if it exists). In this case, the ORs will be executed one after the other.

Gordon Linoff Over a year ago

@EugenRieck . . . I can't speak to this specific instance, but I have at times had to use union all to get multiple conditions connected by or's to use indexes.

Eugen Rieck Over a year ago

Your edited answer looks like it combines all the good stuff - the OP should accept it.

Collectives™ on Stack Overflow

MySQL: find a match with numeric pattern

2 Answers 2

3 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related