Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    78
    Rep Power
    0

    Strip out all non-numerics except the last decimal


    Hi, I been trying all ways to be able to post to mysql a price wich may have and wich may not have decimals.
    For the database I need as value 1000, 1000.00 or for exampel 1000.50
    The problem I have found is that when I write in the form the price like this 1.000.50 then when I try to format I get 1.00
    Is there a way to exclude all dots except the digitdot, changing coma for dot etc.
    Thanks
  2. #2
  3. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6352
    So wait...you're writing the price wrong, and you want a regexp to be able to tell what you really meant?

    MySQL accepts the standard american way of writing numbers: A dot as the decimal separator, and no thousands separator. If you're storing your numbers as strings, stop doing that. Store them as numbers, insert them as numbers, and use your chosen language's built-in number formatting functions (like PHP's number_format) to format the number properly for display.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    78
    Rep Power
    0
    Originally Posted by ManiacDan
    So wait...you're writing the price wrong, and you want a regexp to be able to tell what you really meant?
    Not sure if I get you, I am not writing the price, I have an input where visitors should write the price on the form, and they could be from any country, with our wihtout decimals using dots for thousand or decimals etc. so all kind of ways are expected. The problem I have is to give the price that the customer wrote in form the correct format.
    Thanks
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,572
    Rep Power
    595
    ManiacDan is thinking in terms of only US standards where 123,456.55 is valid. However, he is not considering that outside the US the same number is valid when written as 123.456.55 for example. That is particularity true when expressing euros rather than usd.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by gw1500se
    However, he is not considering that outside the US the same number is valid when written as 123.456.55 for example. That is particularity true when expressing euros rather than usd.
    No, that's not true. In Europe, we use the comma as the decimal separator and the dot as the thousands separator (occasionally).

    So the number above would be written as
    Code:
    123.456,55
    Two dots with different meanings lead to ambiguous expression, so I'm pretty sure no country uses that.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,572
    Rep Power
    595
    I stand corrected as you would know better than me, however, I have seen it used with just decimals but perhaps I misunderstood the meaning of the last decimal point and it was entirely an integer value. Although why there were just 2 digits after the last decimal seems strange.

    In any case that further complicates the problem since the code would need to identify the format before trying to convert it to decimal. If it cannot determine the appropriate format (mine for example) then it would have to result in a verify error.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    78
    Rep Power
    0
    Originally Posted by Jacques1
    So the number above would be written as
    Code:
    123.456,55
    Two dots with different meanings lead to ambiguous expression, so I'm pretty sure no country uses that.
    Maybe no country does, however persons do, as one most of the times only write short prices such as 10.85 then without thinking its easy to use the same for thousands.
    Thanks everybody
  14. #8
  15. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6352
    What I'm saying is: sanitize this data on the way in. You seem to be saying you have the string "123.456.78" in your database. That is not a number, and no database will ever consider that a number. If you're storing numbers like that, you're storing them wrong. I understand that europeans write "123.456,78" when americans write "123,456.78", but neither of those are valid numeric datatypes in a database.

    However, you still need something to sanitize this number on the way in so that the database and code will accept it as a number.

    Now, if you want a regex that formats numbers like this properly:
    PHP Code:
    function strip($a) { 
       return 
    preg_replace("/^(.+?)([\.,](\d{2}))?$/e""str_replace(array(',','.'),'','\\1') . str_replace(',','.','\\2')"$a); 
    }
    echo 
    strip('123,456,78');
    #123456.78
    echo strip('123,456.78');
    #123456.78
    echo strip('123.456,78');
    #123456.78 
    This relies on PHP's extended/evaluated regexps, since it simply takes everything up to the decimal and removes all formatting, then changes the comma to a dot in the case of european formatting.

    Linking requinix to this thread in case there's a better pure regex solution.
    Last edited by ManiacDan; January 17th, 2013 at 02:36 PM.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  16. #9
  17. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    You cannot parse complete nonsense. Allowing different formats is already a very, very questionable approach, because you cannot reliably determine the format.

    Take "1,000": Is that really 1000? Might as well be 1 using European notation and trailing zeros (which is mathematically correct). When you even allow broken expressions like "1.000.50", you can only guess what that might mean.

    That's not a good idea when it comes to money. You want to be absolutely sure to get the right amount and not have your fancy regexes guess some number. The most robust solution is to actually have to separate fields. And you had that already. The only reason you gave it up is because you couldn't get it working (which is a bad reason to not use an idea).
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    78
    Rep Power
    0
    Originally Posted by ManiacDan
    What I'm saying is: sanitize this data on the way in. You seem to be saying you have the string "123.456.78" in your database.
    Thanks, but I dont have the string 123.456.78.
    Normally I insert them in my intranet such as 1000.50 however that is not the case.
    Now I am doing a gatewaypayment for clients and I want to prevent that they canīt enter values like 123.456.78
    its just for validating a form, I need to transform 1.234.56 to 1234.56 same as 1.234,56 or 1234,56 needs to be transformed to 1234.56
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    78
    Rep Power
    0
    Originally Posted by Jacques1
    The most robust solution is to actually have to separate fields. And you had that already. The only reason you gave it up is because you couldn't get it working (which is a bad reason to not use an idea).
    That was my initial idea, however if I have the decimals in a second forminput, if they leave it in blank as the price could be 1000 without any decimal, then my validation got an error as the form does not pass a blank field as a value. I could not find out how to transform a blank field to .00 just in order to validate the form.
  22. #12
  23. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by newtonperri
    That was my initial idea, however if I have the decimals in a second forminput, if they leave it in blank as the price could be 1000 without any decimal, then my validation got an error
    Then that's what you need to fix. And it's actually pretty simple. Just put the decimal part into an "if": if the decimal part is set, then construct a float out of both parts.
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    78
    Rep Power
    0
    Originally Posted by Jacques1
    Then that's what you need to fix. And it's actually pretty simple. Just put the decimal part into an "if": if the decimal part is set, then construct a float out of both parts.
    Think I tried all kind of if's, except the correct one I guess.
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    78
    Rep Power
    0
    Originally Posted by ManiacDan
    Now, if you want a regex that formats numbers like this properly:
    PHP Code:
    function strip($a) { 
       return 
    preg_replace("/^(.+?)([\.,](\d{2}))?$/e""str_replace(array(',','.'),'','\\1') . str_replace(',','.','\\2')"$a); 
    }
    echo 
    strip('123,456,78');
    #123456.78
    echo strip('123,456.78');
    #123456.78
    echo strip('123.456,78');
    #123456.78 
    Thanks,
    are the echo`s supposed to be printed out?
    They do not print out to me
    Last edited by ManiacDan; January 17th, 2013 at 02:36 PM.
  28. #15
  29. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    So what solution are you going now with? You cannot have both.

    Also please don't make a full quote in every reply. Only quote the relevant parts.
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo