Regex to allow ONLY zero before 2 decimal points-CodePudding

I want these to be valid .10
0.10
.99
0.99
and this not valid 1
1.00
9.00

^\d{0,0}(.\d{1,2})?$ ^\d{0,1}(.\d{1,2})?$

I want the user to be able to 0.99 but not 1.99 I am using this regex in a vendor basec system.

I get an error in the system, saying 0.11 is invalid using ^\d{0,0}(.\d{1,2})?$ All numbers 0.99 to 9.99 are valid using ^\d{0,1}(.\d{1,2})?$

I want the user to be able to enter .11 and 0.11 which I am cannot do Help is parreciated.

CodePudding user response：

If you are validating actual numbers then just use an inequality:

SELECT *
FROM yourTable
WHERE num < 1.00;

Otherwise, if you really have to validate number strings as being less than 1 and having 1 or 2 decimal places, with optional leading zero, then use ^0?\.\d{1,2}$:

SELECT *
FROM yourTable
WHERE REGEXP_LIKE(num, '^0?\.\d{1,2}$');

CodePudding user response：

You do not need (slow) regular expressions and can use simple comparisons and, to check the number of decimal places, the ROUND function:

SELECT *
FROM   table_name
WHERE  value >= 0
AND    value < 1
AND    value = ROUND(value, 2)