How do I create a regex that matches (p,s) precision and scale like the SQL decimal type?

Precision (p): Total number of digits to the left and right of the decimal

Scale (s): Total number of digits to the right of the decimal

Consider my following regex so far:

^-?[0-9]+(\.[0-9]{1,3})?$

-? optional negative number
[0-9] matches numbers 0-9
"+" any number of digits
"(\.[0-9]{1,3})?" optional decimal with 1-3 digits

Example:

100.95 has a precision of 5 and a scale of 2 (5,2)

I know how to restrict total numbers to the left, and total numbers to the right, but not sure how to encapsulate the entire value to limit the "p, precision" part, and ignore the period if it exists in that count. The - also needs to be ignored in that total count.

UPDATE:

This seems to be working...

^(?=(\D*\d\D*){0,5}$)-?([0-9]+)?(\.?[0-9]{0,2})?$

blank line matches
0 - match
1 - match
123 - match
123.12 - match
-1 - match
123.122 - no match

Answer

April 13, 2019

KyleMit

When using the type <code>numeric(<precision>[,<scale>])</code> (note: numeric and decimal are synonyms), SQL Server actually stores a fixed number of spaces to the left and right of the decimal point.

So for the following type: numeric(5,2)

SQL allocates up to 2 digits to the right of the decimal
SQL allocates up to 5-2 = 3 digits to the left of the decimal

Meaning 1234.1 is akin to 1234.10 and is invalid!

-- Will throw an Arithmetic Overflow Exception
DECLARE @Price AS NUMERIC(5,2) = 1234.1

So the regex to verify this is simpler than some of the examples here

Look for a number \d and you're allowed 0 to 3 of them {0,3}
Then optionally ?, you can have a period \. and then 0 to 2 more numbers \d{0,2}

The whole regex should look like this:

Further Reading: