Regex - price validation for Mysql decimal(12,2)

Asked

Viewed 116 times

0

I have a passion where there’s a field price which will be completed by the user. The goal is to accept some entries and block others (validation). The value will be stored in a Mysql database, in a field of type decimal (12,2). This means that this field will accept a total value of 12 digits, being 2 decimals. Ex1: 1111111111 will be saved in Mysql as 1111111111.00. That is, even if the user is not among the pennies, the accuracy of . 00 will be saved. Ex2: 1234567890.00 is a valid value while 12345678901.00 is not since exceeded 12 digits. Ex3:. 23 is a valid value because Mysql transforms to 0.23 within the table. But "." (only the point) is not valid, while . 0 is valid (will be saved as 0.00).

So the rules are as follows::

  1. Accept a value where there are only digits or digits and ".".
  2. Not only accepts ".".
  3. If there is "." there can be only two digits at most after it.
  4. Before "." there may only be a maximum of 10 digits.
  5. If there is no "." there can be only a maximum of 10 digits.

For that filter I have the following regex: ^\d{0,8}?(\.)?(\d{1,2})?$ link

It is working for all scenarios except for these:

  1. Which does not have "." it accepts up to 12 digits. Thus 12345678901 should not accept as it has 11 digits.
  2. The point alone is being accepted but should not "."

Everything else seems okay. Someone knows how to fix this?

NOTE:

Although it is not important for this question, I will post the javascript function that uses the regex parameter. It is working correctly. Adds a bootstrap tooltip when the user inserts something not expected by the regex standard

$('#txt_preco').bind({             

             keyup:function(){

                   var objectEvent=$(this);
                   var ck_input = /^\d+$/;
                   var input = $.trim(objectEvent.val());
                   var validationTest =ck_input.test(input);                 

                   //QTY field Validation --------------------------------------------------------------------------------------------------
                   if(!validationTest||input==='0'){//If not match ck_input
                         //alert("algo errado"+input);

                                 objectEvent.val('');//Clear input field
                                 $(this).attr('data-original-title','Oops! Only numbers 1-9 are allowed');
                                 $(this).tooltip('show');
                                 setTimeout( function(){ 
                                       objectEvent.tooltip('hide');
                                       objectEvent.removeAttr('title');
                                       objectEvent.removeAttr('data-original-title'); 
                                   }  , 2500 ); //Wait 2,5 seconds                                                      


                   }
                   ///////////////////////////////////////////////////////////////////////////////////////////
                   else{//If validation match ck_input


                         objectEvent.tooltip('hide');
                                            objectEvent.removeAttr('title');
                                            objectEvent.removeAttr('data-original-title');   

                   }//End of else if(!validationTest||input==='0'){
             }//Fim keyUp
    });//End of $('.txt_qty').bind({
regex
  • 4

    Just one observation: normally the processing of data in the form must be done to preserve the UX, for the user to fill in in the conventional way. Converting the format to Mysql should be the responsibility of the software (i.e., the programmer, and not the user) at Insert time. Not that you can’t use Regex for this, but it seems to me to have a mixture of two completely different concepts in the question (data input vs. storage format). The software region uses . as decimal separator? In Brazil, for example, the correct separator is ,.

  • Correct. This is an optional field, but if it is filled will be treated. There is a javascript function (which I didn’t post here) that will inform the user of the correct model if they make a mistake. through a tooltip. And as for the tab, I am developing something for a client from outside Brazil. My approach is to filter as far as possible with REGEX to decrease the lines of code (without prejudice to UX)

  • It might be nice to mention this in the question, because if you’re going to validate it by html you can’t have much freedom, but if you’re going to use it in JS, you might have something better than just Regex to validate the field. In fact, in JS you can even make the field react by typing in it.

  • this here is an example of how to change the data at the time of typing, is to take zeros to the left, less when there is a point. It could be adapted to do something about all invalid characters, for example: http://answall.com/a/118775/70

  • I can post but the function itself is not the problem. It simply returns true or false based on your search criteria. And yes, the javascript response when typing in it is a tooltip.

  • I only commented on the function, because it often has as part of the conditions with string logic, instead of using Regex, but of course you have to evaluate what is best.

  • 2

    See if this resolves: ^\d{0,8}(\.\d{1,2})? - test: http://www.regexr.com/3deuh

  • Your default {0.8} is not picking up 10 digits before the point. But changing to {0.10} solves. The only thing missing is the digit match with a dot. https://regex101.com/r/sB7qT6/3

  • as "Missing"? It picks up, the different color is only because of the parentheses, for being a capture group.

  • see the last line of the link I sent. https://regex101.com/r/sB7qT6/3 it should take point tbm

Show 5 more comments

1 answer

0

Okay, here’s the pattern sought. I’ll buy it for whoever’s interested.

^(?!\.$)\d{0,10}(?:\.(?:\d\d?)?)?$

https://regex101.com/r/yX1kF6/4

And here is the full function to validate this type of input:

$('#txt_preco').bind({             

             keyup:function(){

                   var objectEvent=$(this);
                   var ck_input = /^(?!\.$)\d{0,10}(?:\.(?:\d\d?)?)?$/;
                   var input = $.trim(objectEvent.val());
                   var validationTest =ck_input.test(input);                 

                   //QTY field Validation --------------------------------------------------------------------------------------------------
                   if(!validationTest||input==='0'){//If not match ck_input
                         //alert("algo errado"+input);

                                 objectEvent.val('');//Clear input field
                                 $(this).attr('data-original-title','Oops! Only numbers 1-9 are allowed');
                                 $(this).tooltip('show');
                                 setTimeout( function(){ 
                                       objectEvent.tooltip('hide');
                                       objectEvent.removeAttr('title');
                                       objectEvent.removeAttr('data-original-title'); 
                                   }  , 2500 ); //Wait 2,5 seconds                                                      


                   }
                   ///////////////////////////////////////////////////////////////////////////////////////////
                   else{//If validation match ck_input


                         objectEvent.tooltip('hide');
                                            objectEvent.removeAttr('title');
                                            objectEvent.removeAttr('data-original-title');   

                   }//End of else if(!validationTest||input==='0'){
             }//Fim keyUp
    });//End of $('.txt_qty').bind({

Browser other questions tagged regex

You are not signed in. Login or sign up in order to post.