I have in the name column:

3 boxes of frogs
7 boxes of frogs
48 boxes of frogs

When I execute:

SELECT name FROM items WHERE name REGEXP "[^1-5] boxes"

...the result is:

7 boxes of frogs
48 boxes of frogs

When I tried:

SELECT name FROM items WHERE name REGEXP "[^1-50] boxes"

and

SELECT name FROM items WHERE name REGEXP "[^1-48] boxes"

...the result is:

7 boxes of frogs
48 boxes of frogs

and

7 boxes of frogs

...respectively.

Why won't "3 boxes of frogs" show with REGEXP "[^1-48] boxes and others. The result of REGEXP "[^1-48] boxes and REGEXP "[^1-50] boxes is so weird. Can someone explain why?

  • If you want SQL to look at the number, then put the number in its own column as you insert the data. – Rick James May 25 '16 at 0:44

Regex doesn't work like that. You cannot specify numerical range.

[] brackets specifies character sets to match. ^ character inside [] means negation = anything outside the specified character set. I assume you wanted to indicate the start of the string which is also marked with ^ but outside of the [] part.

  • [^1-5] means any character which is not a number between 1 and 5
  • [^1-50] means any character which is not a number between 1 and 5 and not 0

In more details:

[^1-5] boxes

7 boxes of frogs matches because 7 is not a number between 1 and 5 followed by a spaces and the word boxes

48 boxes of frogs matches because 4 doesn't match (regular machine not in the first step) -> 8 is not a number between 1 and 5 followed by a spaces and the word boxes the actual match is: 8 boxes of frogs

[^1-48] boxes

Doesn't match 48 boxes of frogs because the rules specifies now characters from the set of (1,2,3,4,8) and the string fails on this check.

Fix your regex

In your case probably you want to use name regexp '^[0-9]+ boxes of frog'

  • how can i use it for 2 digit numbers? – Marr May 13 '16 at 12:52
  • @JhomarMaravillas it is not a calculator. There is an answer detailing ranges and how to parse them. – jkavalik May 14 '16 at 10:45
  • For two digit numbers you can just specify the number of characters: [0-9]{1-2} which will match every one or two digit string including "06" for example. Or a bit more advanced: [1-9]?[0-9] which matches numbers from 0 to 99. – Károly Nagy May 14 '16 at 13:58

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.