0

Hello everyone I have field where user can enter dollar amount. I use JQuery mask plugin to format the number. Here is example of the values that user enters in the input field:

30,000.00
7,513.12
100,324.23
90,234,232.34

Before I save the value in database I would like to remove all characters but numbers (period and commas in this case). I use ColdFusion 2018 and function reReplace() for this purpuse. Here is example of what I have:

reReplace(form.amount, ".\,", "", "all")

The function above will provide these outputs:

30000.00
7513.12
100324.23
90234232.34

As you can see period is still not removed. Is there a way to get rid of everything but numbers?

espresso_coffee
  • 5,980
  • 11
  • 83
  • 193
  • 4
    `reReplace(form.amount, "[.,]", "", "all")`, `reReplace(form.amount, "[^0-9]", "", "all")` – Wiktor Stribiżew Oct 11 '19 at 15:02
  • 1
    Duplicate of [Need a regex to remove everything except numbers](https://stackoverflow.com/questions/27772805/need-a-regex-to-remove-everything-except-numbers) – Wiktor Stribiżew Oct 11 '19 at 15:05
  • 1
    @WiktorStribiżew fyi... would be a good idea to add those in an answer instead of comment – Dan Roberts Oct 11 '19 at 15:14
  • If you are inserting into a database, what is the datatype in the db? Do you store the entire number as an integer? Sybase has a `money` datatype, and it would probably be better to use that. What happens of someone enters just `$30,000`? That's a lot different than stripping characters from `$30,000.00`. – Shawn Oct 11 '19 at 16:06
  • @Shawn I have INTEGER datatype for the amount field. Also, I mentioned that I use JQuery mask on the front end to format the number. Scenario that you explained would be if someone tries to hack front end. That would lead to the situation where number would probably has wrong format. – espresso_coffee Oct 11 '19 at 16:13
  • 1
    Using front end js to validate data asks for trouble. Someone doesn't have to "hack". All that needs to happen is they have Javascript disabled/blocked/disallowed/limited. Regardless, is there a business need to run as `INTEGER`? You're usually better off using the datatype intended for the type of data you're using. If you're working with a value that has a decimal component, use a datatype that deals with decimals. – Shawn Oct 11 '19 at 16:22
  • @Shawn If I change from INTEGER to DECIMAL(13,2) should I still trim the values as I showed in my example above before values get to the database? Thank you. – espresso_coffee Oct 11 '19 at 16:53
  • Leave the decimals in. But you'd probably also want to clean them up on server-side before trying to insert them. – Shawn Oct 11 '19 at 16:58
  • @Shawn That's exactly what I asked. On the server side value should be just a number. If value submitted looks like this `8,500.00` then before value gets saved to db it should look like this 850000, correct? – espresso_coffee Oct 11 '19 at 17:01
  • 1
    I wouldn't. I'd submit `8500.00` to a decimal datatype. – Shawn Oct 11 '19 at 19:00

0 Answers0