SfC Home > ColdFusion >

Explanation of how to change data from a text string to the telephone format in ColdFusion. Also refer to database, record, row, field, column, CFINPUT, INSERT function, CFML, Ron Kurtus, School for Champions. Copyright © Restrictions

Changing Data to Telephone Format in ColdFusion

by Ron Kurtus (14 December 2005)

Sometimes telephone numbers are stored in a database as a text string of digits, such as 4145551234. It can be difficult to display those numbers in the standard telephone format. Also, data-entry can be confusing, with people entering in different formats. A preferred format would include dashes, such as 414-555-1234. A method to include the dashes is to first create a new row in your database table and then use the INSERT function to include the dashes. Test this by adding new telephone numbers.

Questions you may have include:

• How do you create a new row in a table?
• How can you convert that text string into a telephone format?
• How can you make sure the data is always entered properly?

This lesson will answer those questions. There is a mini-quiz near the end of the lesson.

Create new row

It is a good idea to create a new telephone number row or field, in which to work, just in case you screw up. Let's name it TelephoneNo2.

--> NOTE: How do you create a new row? Maybe have a separate lesson on that.

--> You could also just back up your table or database

Make a simple ColdFusion page to copy the numbers from one row to another. Again, so you don't accidentally delete your list, do a test case for a single ID.

<CFQUERY DATASOURCE="xxx">
UPDATE TableName
SET TelephoneNo2 = TelephoneNo
WHERE ID = 25
</CFQUERY>

If it works correctly, get rid of WHERE ID = 25 and run the UPDATE query again, populating the new row.

Convert strings

The method to convert 4145551234 into 414-555-1234 is to use the INSERT function in a query.

INSERT("-", "4145551234", 3) will insert "-" 3 spaces from the front of the 4145551234 to give you 414-5551234. You can then next the INSERT function to add the second dash.

You query for the Telephone values, then update them with INSERT to add the dashes. You have the backup TelephoneNo2 in case things don't work as planned.

Query for values

<CFQUERY NAME="tel" DATASOURCE="xxx">
SELECT ID, TelephoneNo
FROM TableName
WHERE TelephoneNo <> ' '
</CFQUERY>

 Checking TelephoneNo for not equal to ' ' (empty) is done to avoid errors for records with no telephone number. Instead of ' ', you may have to use NULL, depending on how your database is set up. Typcially a MS SQL database requires the use of NULL.

Loop and update

Next, you loop through each item in the query and update the TelephoneNo row.  Note how the INSERT function is nested to add the two dashes in the correct position. You also must include ID = #ID#, otherwise every new value will be updated to the last one in the column.

<CFLOOP QUERY="tel">

<CFQUERY DATASOURCE="xxx">
UPDATE TableName
SET TelephoneNo = '#INSERT("-", INSERT("-", TelephoneNo, 3), 7)#'
WHERE TelephoneNo <> ' '
AND ID = #ID#
</CFQUERY>

</CFLOOP>

Delete backup

If everything worked fine, you can delete your backup TelephoneNo2 row . 

Adding telephone numbers

To make sure new data is added to the database in the correct telephone format, you should use the following CFINPUT statement:

<CFINPUT TYPE="text" NAME="TelephoneNo"  VALIDATE="telephone" MESSAGE="You must enter the phone number in the xxx-xxx-xxxx format" SIZE="12">

If the data is not entered in the proper format, a warning message will appear. 

Other methods

I am sure there are other ways to perform this task or to solve this problem. This one seems to work pretty good, with no too much pain.

Summary

To convert a text string of digits to the telephone format, you can use a nested INSERT function and add the required dashes. It is a good idea to make a backup row before making your changes, in case things don't work as planned. To  input data, use VALIDATE="telephone" in CFINPUT.

Answers to Readers' Questions

Be honorable in your work

Resources

The following resources provide information on this subject.

Websites

ColdFusion Resources

Books

Top-rated books on ColdFusion

Miscellaneous

Mini-quiz to check your understanding

1. Why do you create a new row?

2. What is needed to display ColdFusion pages on the Web?

3. What is a major application of ColdFusion?

If you got all three correct, you are on your way to becoming a Champion in ColdFusion Development. If you had problems, you had better look over the material again.

What do you think?

Do you have any questions, comments, or opinions on this subject? If so, send an email with your feedback. We will try to get back to you as soon as possible.

Share link

Feel free to establish a link from your website to pages in this site.

Or use our form to send this link to yourself or a friend.

Students and researchers

The Web address of this page is
www.school-for-champions.com/coldfusion/telephone.htm.

Please include it as a reference in your report, document, or thesis.

Where can you go from here?

School for Champions

ColdFusion topics

Changing Data to Telephone Format in ColdFusion