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

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.

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

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.

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


Two dots with different meanings lead to ambiguous expression, so I'm pretty sure no country uses that.

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.

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

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:


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.

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).

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

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.

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.

Thanks,
are the echo`s supposed to be printed out?
They do not print out to me

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.