The purpose of this article is to show how to use Coldfusion to break up a 10 digit string of number (an unformatted phone number) into three parts being the area code, prefix, and line number. Doing this allows you to format your numbers however you wish.
So it seems like I find myself being given a spreadsheet with a set of phone numbers in about ten different formats. This is of course makes no sense when we should want our phone number as raw data. So Excel, or whatever spreadsheet software is being used, should be able to handle getting rid of the formatting with ‘find & replace’. Once the formatting has been removed we can work with it in Coldfusion.
Just a note that the easiest way to deal with this data is in a database and not in a spreadsheet. There’s probably a better way to do this, but you can export your list of unformatted phone numbers from excel to a .csv file format. This is just a comma delimited list of data from the spreadsheet. Once you have a .csv file import it into an sql table that you’ve created. If you save your .csv file to your desktop and you’re using a mac you can use the following SQL statement:
INTO TABLE schema_name.phone_numbers_tbl
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\r’
Just make sure that the second directory here matches the name of your computer, and the name of the .csv file matches the name of your file. Also make note that the ‘schema_name’ will need to match whatever the schema name is that holds your table that you’re storing the phone numbers in.
Break up the number
Now we can use Coldfusion to break up our number into the three parts of a phone number. I’ve written a very simple function that will do this to any number passed in. So let’s take a look.
divide_phone_number.cfc
<!— ******************************** DIVIDE PHONE NUMBER ******************************** —>
<cffunction name=”divideNumber” displayname=”Divide Phone Number” description=”Divides a 10 digit number into the three parts of a standard phone number.” access=”public” output=”false” returntype=”struct”>
<!— pass in the number to be divided —>
<cfargument name=”number” displayName=”Phone Number” type=”string” hint=”Number to be divided” required=”true” />
<!— create a struct to return —>
<cfset VARIABLES.number = structnew()>
<!— set the three parts of our struct (the three parts of our phone number) —>
<cfset VARIABLES.number.areacode = “”>
<cfset VARIABLES.number.prefix = “”>
<cfset VARIABLES.number.linenumber = “”>
<!— get the area code from our number passed in —>
<cfset VARIABLES.number.areacode = left(ARGUMENTS.number, 3)>
<!— get the prefix from our number passed in. —>
<cfset VARIABLES.number.prefix = mid(ARGUMENTS.number, 3, 3)>
<!— get the line number from our number passed in —>
<cfset VARIABLES.number.linenumber = right(ARGUMENTS.number, 4)>
<!— return our struct —>
<cfreturn VARIABLES.number>
</cffunction>
</cfcomponent>
We have here a function that accepts a single number. You may want to do some validation on it just to make sure that it’s numeric and 10 digits long. Otherwise, I’ll just assume that we’re not passing in anything except a 10 digit string of numbers. I create a simple struct that holds the area code, prefix, and line number. I then set the appropriate values into the struct using three related methods. These are the ‘left()’, ‘mid()’, and ‘right()’. The left() method will return a specified number of digits from the left side of the string. The right method does, obviously the opposite by returning a specified number of digits from the right side of the string. And lastly the mid() method returns a specified number of digits from anywhere in the middle of the string.
So we know that the first three digits are our area code. To get this we tell Coldfusion to use the left() method to return the first three digits of our phone number (here represented as ‘ARGUMENTS.number’). We also use the right() method to tell Coldfusion to return the last four digits, which is our line number. Lastly, the mid() method is slightly different in that it requires the string to return values from, a starting position, and how many characters to return. So in our case we know that the prefix begins in the fourth position. We use the mid() method to tell Coldfusion to start at position 4 and return 3 characters starting with the 4th position.(Note that unlike javascript, actionscript, and many other languages the array of letters begins at 1 and not 0. Isn’t this nice! I don’t know about others, but I’m not in a habit of counting from 0.)
Lastly we return our struct containing our three sets of values.
Now if we call the method using the following code we will get our formatted numbers.
divide_phone_number.cfm
<cfset VARIABLES.originalNumber = 1235559876>
<cfinvoke component=”divide_phone_number”
method=”divideNumber”
number=”#VARIABLES.originalNumber#”
returnvariable=”VARIABLES.phoneNumber”>
<cfoutput>
<i>Original Number:</i> #VARIABLES.originalNumber# <br /><br />
<strong>Format 1:</strong> (#VARIABLES.phoneNumber.areacode#)#VARIABLES.phoneNumber.prefix#-#VARIABLES.phoneNumber.linenumber# <br />
<strong>Format 2:</strong> #VARIABLES.phoneNumber.areacode#.#VARIABLES.phoneNumber.prefix#.#VARIABLES.phoneNumber.linenumber#<br />
<strong>Format 3:</strong> #VARIABLES.phoneNumber.areacode#-#VARIABLES.phoneNumber.prefix#-#VARIABLES.phoneNumber.linenumber#<br />
</cfoutput>
What is returned are three common formats for phone numbers:
Original Number: 1235559876
Format 1: (123)555-9876
Format 2: 123.555.9876
Format 3: 123-555-9876