2

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
user1447679
  • 3,076
  • 7
  • 32
  • 69

4 Answers4

2

You could use a lookahead assertion at the very beginning to verify there are exactly as many digits in the string as you require.

(?=(\D*\d\D*){5}$)

That would make your entire expression this:

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

(I'm using the shorthand character classes \d (digit) and \D (non-digit) for brevity and, IMO, readability.)

It matches a digit \d, possibly surrounded by non-digit characters \D* we don't care about, and makes sure that matches exactly five times {5} before the $ end of the string.

Update:

Here's the expression you settled on, simplified with a few tweaks:

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

\d is the same as [0-9], so you have some redundancy that can be removed.

  • ([\d0-9]+)? could really just be \d* (not sure if you're actually using the capture group, in which case you should leave the parentheses around it: (\d*) )
  • \.? doesn't need the ? since it could be followed by 0 digits and the group it's in has a ? already
  • [\d0-9]{0,2} can just be \d{0,2}
Abdul Aziz Barkat
  • 19,475
  • 3
  • 20
  • 33
Wiseguy
  • 20,522
  • 8
  • 65
  • 81
  • This is super close and allowed me to fine tune for my needs. If you can update your answer to include this I'd be glad to accept it, as a slightly closer read on the requirements for the p/s would have worked. Although my answer may still have issues. ^(?=(\D*\d\D*){0,5}$)-?([\d0-9]+)?(\.?[\d0-9]{0,2})?$ – user1447679 Apr 12 '16 at 15:53
  • @user1447679 Ah, I see what you mean. Updated and tweaked. – Wiseguy Apr 13 '16 at 14:35
  • Thanks. Yes, I think I'll go with 0-9. \d allows other types of values other than 0-9 I think, doesn't it? – user1447679 Apr 13 '16 at 17:28
  • I think I fine tuned it a bit more. ^(?=(\D*[0-9]){0,5}$)-?[0-9]*(\.[0-9]{0,2})?$ This way a period isn't allowed unless it's followed by digits. Thoughts? I know it's resolved, but if you have time to offer more feed back on the latest, I'd appreciate it. – user1447679 Apr 13 '16 at 17:37
  • @user1447679 Actually, according the link I shared to [shorthand character classes](http://www.regular-expressions.info/shorthand.html), `\d` is equivalent to `[0-9]`. Nothing wrong with just using `[0-9]` though. (You might be thinking of word character `\w`, which does include more than just letters A-Z.) As for the period on the end -- yep, that works. – Wiseguy Apr 13 '16 at 18:34
  • Had to alter this slightly again due to a silly mistake re: SQL. Need to subtract the scale from precision to get the max allowed digits to the left: ^(?=(\D*\d){0,5}$)-?\d{0,3}(\.\d{0,2})?$ – user1447679 Apr 15 '16 at 17:19
1

When using the type numeric(<precision>[,<scale>]) (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:

\d{0,3}(\.\d{0,2})?

Further Reading:

KyleMit
  • 30,350
  • 66
  • 462
  • 664
  • This is the answer (for me) because if I have a (p,s) of (7,2) for my column, the accepted answer allows 1234567 which is invalid – Healyhatman Nov 01 '21 at 05:37
0

As a supplement of previous answer, using lookahead too. To restrict the number of total digits in the whole number, you may use (?=(?:-?[\.0-9]{3,8}$)|(?:-?[0-9]{1,7}$)). 3 means at least 1 precision, 1 scale is needed (it's 3 with the decimal point); 7 means a whole number with 7 precision.

There are probably minor edge cases need to be taken care of.

See RegEx101.

dz902
  • 4,782
  • 38
  • 41
0

If you want to limit the "p, precision" part (e.g., you want to limit to precision as 5), you could do:

^-?(\d{5}|\d{2}(?=\d*\.\d*)[\d.]{3}\d)$

^               # match start of line
-?              # match - literally; zero or one time
(               # capturing group starts
\d{5}|          # match 5 digits (such as, 10095); OR
\d{2}           # match 2 digits; assert at least 2 digits ahead of dot (.)
(?=\d*\.\d*)    # positive lookahead; assert . (dot) can be matched ahead
[\d.]{3}        # match a digit or . (dot) three times
\d              # assert at least one digit at the end
)               # capturing group ends
$               # match end of line

REGEX 101 DEMO

Quinn
  • 4,394
  • 2
  • 21
  • 19