Changing money to whole numbers for databases

17Oct08

Ever have a textbox where a person needs to enter a number, and they enter something like… $24.56? Or even 45,898.68? I have. The worst is that I wasn’t working on a database I created, and I further needed the dollar entered to be a whole number – without changing a lot of stuff =). I could have used money or decimal, but the way things were written – that was not an option. A javascript check would also work – but it of course wouldn’t work if turned off. So, off to create a utility function I go!

This can be changed over to CFScript, PHP, or really any other language. For PHP, change the RE* functions to the preg_* functions, for ‘ceiling’ change to ceil, and for ‘val’ change to floatval or intval.

NOTE:

• This is not multi-line compatible but can be change by adding the \m pattern modifier if your language allows it.
• If you do want to allow decimals (if you are using decimal, float, or money format in your database), remove the ceiling function, and the period.
• I used brackets in my regular expression instead of the alternation character so I could add/remove as needed based on locale.
• I did not need to check for negative numbers, so this is purely based on positive numbers but can still be modified to allow negatives by adjusting the regular expression check.

Steps taken:

1. Create a function that takes an argument. In this case the argument I am accepting is a string and it is required. My variable name is ‘thisNumber’.
   • <cffunction name="DollarToWhole" access="public" returntype="numeric" hint="Removes symbols from dollar, rounds up, and returns integer">
     </cffunction>
2. Setup the regular expression checks. isDollarFormat checks that the submitted string is indeed a dollar format. It can include any of the following: dollar sign ($), comma (,), period (.), or and number (\d). It does not HAVE to have anything, but it can have as many of these items as needed (
   *). DollarChars are the common characters that could be entered as a number (dollar sign, comma).
   • <cfset var isDollarFormat = '^[$,.\d]*$'>
   • <cfset var dollarChars = '[$,]'>
3. Remove the parts from DollarChars that I want to be removed ( dollar sign and comma). Since I am rounding up, I do not want to remove the period; I want it to remain a decimal until I get to the end.
   • <!—Remove dollars and commas —>
     <cfset thisNumber = REReplace(thisNumber,dollarChars, '', 'all')>
4. Check to make sure that my entered dollar amount (thisNumber) is a valid dollar number to my specifications in step 2. If not I attempt to get any numbers that are in the beginning of the string entered, if there are no numbers I will make thisNumber zero. (Set this however you want – or throw an error)
   • <!—Check that this is a correct dollar format —>
     <cfif REFind(isDollarFormat,thisNumber) EQ 0><cfset thisNumber = Val(thisNumber)> </cfif>
5. Round the number up to a valid whole number.
   • <!—Round current number up to full dollar —>
     <cfset thisNumber =Ceiling(thisNumber)>
6. Return thisNumber – my whole number without any dollars or commas ready to go into the database!
   • <cfreturn thisNumber>

<cffunction name="DollarToWhole" access="public" returntype="numeric" hint="Removes
symbols from dollar, rounds up, and returns integer">
    <cfargument name="thisNumber" type="string" required="yes">
        <cfset var isDollarFormat = '^[$,.\d]*$'>
        <cfset var dollarChars = '[$,]'>

        <!— Remove dollars and commas —>
        <cfset thisNumber = REReplace(thisNumber, dollarChars, '', 'all')>

        <!— Check that this is a correct dollar format —>
        <cfif REFind(isDollarFormat, thisNumber) EQ 0>
            <cfset thisNumber = Val(thisNumber)>
        </cfif>

        <!— Round current number up to full dollar —>
        <cfset thisNumber = Ceiling(thisNumber)>
    <cfreturn thisNumber>
</cffunction>

Examples:

UNFORMATTED :: $36,000.54
FORMATTED :: 36001
UNFORMATTED :: NA
FORMATTED :: 0
UNFORMATTED :: 32.36 I Think
FORMATTED :: 33

Filed under: ColdFusion, Intermediate, PHP, Regular expressions   |  Leave a Comment