Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Regular expression for 1-50 without decimal point

I am looking for a regular expression that will match any number from 1 to 50 inclusive. So far, I have found examples but they all allow the string to contain a decimal point, which I do not want to include. So 1,13,24,50 are OK but 1. ,etc are not. Is there a REGEXP that I can use?

Thanks in advance, Tim

like image 250
Tim Avatar asked Dec 06 '22 00:12

Tim


2 Answers

Try this:

/^(?:[1-9]|[1-4][0-9]|50)$/

UPDATE:

Now that I see the question has been updated to refer to MySQL, this changes things significantly. The above-mentioned regular expression uses non-capturing parens which are not supported by MySQL. But it also begs the question; should you really be using regular expressions to solve this problem? We really have to look at how you are storing your numbers that must be between 1 and 50. Are they varchars? Are they ints? I'll demonstrate how to solve it both ways. First I'll set up a test table with indexes:

create table regextest (
    id int unsigned primary key auto_increment,
    varchar_number varchar(5) not null,
    int_number int not null,
    index(varchar_number),
    index(int_number)
) engine=innodb;

Now put some test data into it making sure all our edge cases are covered:

insert into regextest (varchar_number, int_number)
    values ('0', 0), ('1', 1), ('35', 35), ('49', 49), ('50', 50), ('51', 51);

And now, here is a query that will solve your problem assuming that your numbers are stored as strings in the varchar_number column:

mysql> select * from regextest where varchar_number rlike '^([1-9]|[1-4][0-9]|50)$';
+----+----------------+------------+
| id | varchar_number | int_number |
+----+----------------+------------+
|  2 | 1              |          1 |
|  3 | 35             |         35 |
|  4 | 49             |         49 |
|  5 | 50             |         50 |
+----+----------------+------------+
4 rows in set (0.00 sec)

This works but it will perform poorly on large data sets because it can't use an index even if one is present. MySQL must run the regular expression once for every row in the table. Suppose your numbers between 1 and 50 were stored as ints in the int_number column. You could simply do this:

mysql> select * from regextest where int_number between 1 and 50;
+----+----------------+------------+
| id | varchar_number | int_number |
+----+----------------+------------+
|  2 | 1              |          1 |
|  3 | 35             |         35 |
|  4 | 49             |         49 |
|  5 | 50             |         50 |
+----+----------------+------------+
4 rows in set (0.00 sec)

This query will perform well because it can use an index and it's also more readable and more maintainable. Wins all around.

like image 96
Asaph Avatar answered Dec 07 '22 20:12

Asaph


'^(0?\d|[1-4]\d|50)$'

That is:

  • The start of the input
  • followed by a single digit (optional preceded by a 0), or
  • followed by a 1-4 followed by any digit, or
  • followed by 50
  • and then ensure that we see the end of the input.

Edit: The above allows 0 (and 00) which you surely don't want. So, assuming you didn't really want leading zeros allowed anyhow:

'^([1-9]|[1-4]\d|50)$'

Edit: As the OP's later comments indicate that this is for MySQL, I've changed the syntax for specifying the pattern.

like image 27
Phrogz Avatar answered Dec 07 '22 20:12

Phrogz