Question 20484 in Mysql Question posted in
The official MySQL documentation can be found here.

Javascript regex to validate a decimal before inserting into MySQL

I have a regex to validate a decimal value before inserting into MySql database, but there is an issue with it. It is allowing a just the negative symbol with no digits after it.

So for example, I’m validating against a decimal(7,2).
Here is my regex:

^[+-]?d{0,5}(?:.d{0,2})?$

And here are the valid/invalid values

Valid
-1
+1 
1
+.1
-.1 
.1
+11111.11
-11111.11
11.11 
11111
+1.
-1.
1.

Invalid 
1111111
11.11111
0.111111 
.1111111
+111111.11
-111111.11
+11111.111
-11111.111
11111.111
111111.11
-
+

The problem is that it shows – and + as valid. How can I get these values to be invalid?

Tags:

3

Answers


  1. 0

    Check if there’s at least one digit at position 0 or 1 or 2 with a lookahead:

    ^(?=.{0,2}d)[+-]?d{0,5}(?:.d{0,2})?$

    or use an alternation as in @trincot answer.

    Login or Signup to reply.
  2. 0

    You can use alternatives:

    • One that requires at least one digit in the integral part of the number with optional decimals
    • One that matches a number that does not have digits in the integral part and has decimal(s)
    ^[-+]?(?:d{1,5}(?:.d{0,2})?|.d{1,2})$
    Login or Signup to reply.
  3. 0

    The "valid values" list shows that you want to match both integer and float numbers with size constraint. The following should work:

    ^[+-]?(d{1,5}|d{0,5}.d{0,2})$

    https://regex101.com/r/NdmIWo/1

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search