Standardize phone numbers in submitted form with ColdFusion

We're looking for a way to standardize the entry of phone numbers entered in a form to be, for example, (444) 555-666. Right now you can enter it as any format, such as 4445556666 or 444-555-6666 and there's no standardization. I'd like a way for the number to be converted to the former format when it's entered into the database.

Here is the HTML code for the form:

<tr>

  <td align="right">

    <label for="phone_number">Telephone:</label>

  </td>

  <td>

    <input type="text" name="phone_number" value="#form.phone_number#" />

  </td>

</tr>

This, along with all other data, is entered into the database using a cfquery INSERT INTO command. I can provide that code if needed. Here's an abbridged version:

INSERT INTO Schedule_Registrations(

  phone_number

)

VALUES(

  '#FORM.phone_number#'

)

Any thoughts on this would be appreciated.

edited Dec 24 '11 at 17:06

ale

5,83155264

asked Dec 24 '11 at 4:01

Nietzsche

84117

You should really be using cfqueryparam. Never insert user-accessible data directly into a query. You are risking a sql injection attack. Replace '#form.phone_number#' with <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.phone_number#" />

– Sean Coyne
Dec 28 '11 at 0:01

add a comment |

Here is the HTML code for the form:

<tr>

  <td align="right">

    <label for="phone_number">Telephone:</label>

  </td>

  <td>

    <input type="text" name="phone_number" value="#form.phone_number#" />

  </td>

</tr>

This, along with all other data, is entered into the database using a cfquery INSERT INTO command. I can provide that code if needed. Here's an abbridged version:

INSERT INTO Schedule_Registrations(

  phone_number

)

VALUES(

  '#FORM.phone_number#'

)

Any thoughts on this would be appreciated.

edited Dec 24 '11 at 17:06

ale

5,83155264

asked Dec 24 '11 at 4:01

Nietzsche

84117

You should really be using cfqueryparam. Never insert user-accessible data directly into a query. You are risking a sql injection attack. Replace '#form.phone_number#' with <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.phone_number#" />

– Sean Coyne
Dec 28 '11 at 0:01

add a comment |

Here is the HTML code for the form:

<tr>

  <td align="right">

    <label for="phone_number">Telephone:</label>

  </td>

  <td>

    <input type="text" name="phone_number" value="#form.phone_number#" />

  </td>

</tr>

This, along with all other data, is entered into the database using a cfquery INSERT INTO command. I can provide that code if needed. Here's an abbridged version:

INSERT INTO Schedule_Registrations(

  phone_number

)

VALUES(

  '#FORM.phone_number#'

)

Any thoughts on this would be appreciated.

edited Dec 24 '11 at 17:06

ale

5,83155264

asked Dec 24 '11 at 4:01

Nietzsche

84117

Here is the HTML code for the form:

<tr>

  <td align="right">

    <label for="phone_number">Telephone:</label>

  </td>

  <td>

    <input type="text" name="phone_number" value="#form.phone_number#" />

  </td>

</tr>

This, along with all other data, is entered into the database using a cfquery INSERT INTO command. I can provide that code if needed. Here's an abbridged version:

INSERT INTO Schedule_Registrations(

  phone_number

)

VALUES(

  '#FORM.phone_number#'

)

Any thoughts on this would be appreciated.

coldfusion coldfusion-9

edited Dec 24 '11 at 17:06

ale

5,83155264

asked Dec 24 '11 at 4:01

Nietzsche

84117

edited Dec 24 '11 at 17:06

ale

5,83155264

asked Dec 24 '11 at 4:01

Nietzsche

84117

edited Dec 24 '11 at 17:06

ale

5,83155264

edited Dec 24 '11 at 17:06

ale

5,83155264

edited Dec 24 '11 at 17:06

ale

5,83155264

asked Dec 24 '11 at 4:01

Nietzsche

84117

asked Dec 24 '11 at 4:01

Nietzsche

84117

asked Dec 24 '11 at 4:01

Nietzsche

84117

You should really be using cfqueryparam. Never insert user-accessible data directly into a query. You are risking a sql injection attack. Replace '#form.phone_number#' with <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.phone_number#" />

– Sean Coyne
Dec 28 '11 at 0:01

add a comment |

You should really be using cfqueryparam. Never insert user-accessible data directly into a query. You are risking a sql injection attack. Replace '#form.phone_number#' with <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.phone_number#" />

– Sean Coyne
Dec 28 '11 at 0:01

You should really be using cfqueryparam. Never insert user-accessible data directly into a query. You are risking a sql injection attack. Replace '#form.phone_number#' with <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.phone_number#" />

– Sean Coyne
Dec 28 '11 at 0:01

add a comment |

1 Answer
1

active

oldest

votes

The way I've chosen to deal with this issue is to store raw numbers in the database, and then display them how I want on a web page. To normalize the numbers, use a simple regex:

<cfset cleanPhoneNumber = reReplace(form.phoneNumber, "[^0-9]", "", "ALL")>

Store cleanPhoneNumber in the database, and then use a simple format function to display it:

<cffunction name="formatPhoneNumber">

    <cfargument name="phoneNumber" required="true">



    <cfif len(phoneNumber) EQ 10>

        <!--- This only works with 10-digit US/Canada phone numbers --->

        <cfreturn "(#left(phoneNumber, 3)#) #mid(phoneNumber, 4, 3)#-#right(phoneNumber, 4)#">

    </cfif>



    <cfreturn phoneNumber>

</cffunction>

The challenge is that outside the US there is not a standardized way to format numbers (that I've found). Also be careful if you allow a user to enter an extension in the phone number field.

edited Nov 20 '18 at 10:35

Mark Skelton

1,77631631

answered Dec 24 '11 at 4:40

Scott Coldwell

764412

Thanks, this works well.

– Nietzsche
Dec 24 '11 at 4:54

I would add, don't clean the number before putting into the DB, clean it when you display it to yourself or others. That way you always have the option to show them what they entered.

– Adrian Lynch
Dec 24 '11 at 20:50

1

I store both the phone number as entered and a cleaned up version. I have an index in the DB on the cleaned version so i can search by phone number a lot faster.

– Yisroel
Dec 25 '11 at 1:14

How do you output the formatted phone number?

– aparker81
Nov 26 '12 at 17:08

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f8622587%2fstandardize-phone-numbers-in-submitted-form-with-coldfusion%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

The way I've chosen to deal with this issue is to store raw numbers in the database, and then display them how I want on a web page. To normalize the numbers, use a simple regex:

<cfset cleanPhoneNumber = reReplace(form.phoneNumber, "[^0-9]", "", "ALL")>

Store cleanPhoneNumber in the database, and then use a simple format function to display it:

<cffunction name="formatPhoneNumber">

    <cfargument name="phoneNumber" required="true">



    <cfif len(phoneNumber) EQ 10>

        <!--- This only works with 10-digit US/Canada phone numbers --->

        <cfreturn "(#left(phoneNumber, 3)#) #mid(phoneNumber, 4, 3)#-#right(phoneNumber, 4)#">

    </cfif>



    <cfreturn phoneNumber>

</cffunction>

The challenge is that outside the US there is not a standardized way to format numbers (that I've found). Also be careful if you allow a user to enter an extension in the phone number field.

edited Nov 20 '18 at 10:35

Mark Skelton

1,77631631

answered Dec 24 '11 at 4:40

Scott Coldwell

764412

Thanks, this works well.

– Nietzsche
Dec 24 '11 at 4:54

I would add, don't clean the number before putting into the DB, clean it when you display it to yourself or others. That way you always have the option to show them what they entered.

– Adrian Lynch
Dec 24 '11 at 20:50

1

I store both the phone number as entered and a cleaned up version. I have an index in the DB on the cleaned version so i can search by phone number a lot faster.

– Yisroel
Dec 25 '11 at 1:14

How do you output the formatted phone number?

– aparker81
Nov 26 '12 at 17:08

add a comment |

The way I've chosen to deal with this issue is to store raw numbers in the database, and then display them how I want on a web page. To normalize the numbers, use a simple regex:

<cfset cleanPhoneNumber = reReplace(form.phoneNumber, "[^0-9]", "", "ALL")>

Store cleanPhoneNumber in the database, and then use a simple format function to display it:

<cffunction name="formatPhoneNumber">

    <cfargument name="phoneNumber" required="true">



    <cfif len(phoneNumber) EQ 10>

        <!--- This only works with 10-digit US/Canada phone numbers --->

        <cfreturn "(#left(phoneNumber, 3)#) #mid(phoneNumber, 4, 3)#-#right(phoneNumber, 4)#">

    </cfif>



    <cfreturn phoneNumber>

</cffunction>

The challenge is that outside the US there is not a standardized way to format numbers (that I've found). Also be careful if you allow a user to enter an extension in the phone number field.

edited Nov 20 '18 at 10:35

Mark Skelton

1,77631631

answered Dec 24 '11 at 4:40

Scott Coldwell

764412

Thanks, this works well.

– Nietzsche
Dec 24 '11 at 4:54

I would add, don't clean the number before putting into the DB, clean it when you display it to yourself or others. That way you always have the option to show them what they entered.

– Adrian Lynch
Dec 24 '11 at 20:50

1

I store both the phone number as entered and a cleaned up version. I have an index in the DB on the cleaned version so i can search by phone number a lot faster.

– Yisroel
Dec 25 '11 at 1:14

How do you output the formatted phone number?

– aparker81
Nov 26 '12 at 17:08

add a comment |

The way I've chosen to deal with this issue is to store raw numbers in the database, and then display them how I want on a web page. To normalize the numbers, use a simple regex:

<cfset cleanPhoneNumber = reReplace(form.phoneNumber, "[^0-9]", "", "ALL")>

Store cleanPhoneNumber in the database, and then use a simple format function to display it:

<cffunction name="formatPhoneNumber">

    <cfargument name="phoneNumber" required="true">



    <cfif len(phoneNumber) EQ 10>

        <!--- This only works with 10-digit US/Canada phone numbers --->

        <cfreturn "(#left(phoneNumber, 3)#) #mid(phoneNumber, 4, 3)#-#right(phoneNumber, 4)#">

    </cfif>



    <cfreturn phoneNumber>

</cffunction>

The challenge is that outside the US there is not a standardized way to format numbers (that I've found). Also be careful if you allow a user to enter an extension in the phone number field.

edited Nov 20 '18 at 10:35

Mark Skelton

1,77631631

answered Dec 24 '11 at 4:40

Scott Coldwell

764412

The way I've chosen to deal with this issue is to store raw numbers in the database, and then display them how I want on a web page. To normalize the numbers, use a simple regex:

<cfset cleanPhoneNumber = reReplace(form.phoneNumber, "[^0-9]", "", "ALL")>

Store cleanPhoneNumber in the database, and then use a simple format function to display it:

<cffunction name="formatPhoneNumber">

    <cfargument name="phoneNumber" required="true">



    <cfif len(phoneNumber) EQ 10>

        <!--- This only works with 10-digit US/Canada phone numbers --->

        <cfreturn "(#left(phoneNumber, 3)#) #mid(phoneNumber, 4, 3)#-#right(phoneNumber, 4)#">

    </cfif>



    <cfreturn phoneNumber>

</cffunction>

The challenge is that outside the US there is not a standardized way to format numbers (that I've found). Also be careful if you allow a user to enter an extension in the phone number field.

edited Nov 20 '18 at 10:35

Mark Skelton

1,77631631

answered Dec 24 '11 at 4:40

Scott Coldwell

764412

edited Nov 20 '18 at 10:35

Mark Skelton

1,77631631

edited Nov 20 '18 at 10:35

Mark Skelton

1,77631631

edited Nov 20 '18 at 10:35

Mark Skelton

1,77631631

answered Dec 24 '11 at 4:40

Scott Coldwell

764412

answered Dec 24 '11 at 4:40

Scott Coldwell

764412

answered Dec 24 '11 at 4:40

Scott Coldwell

764412

Thanks, this works well.

– Nietzsche
Dec 24 '11 at 4:54

I would add, don't clean the number before putting into the DB, clean it when you display it to yourself or others. That way you always have the option to show them what they entered.

– Adrian Lynch
Dec 24 '11 at 20:50

1

I store both the phone number as entered and a cleaned up version. I have an index in the DB on the cleaned version so i can search by phone number a lot faster.

– Yisroel
Dec 25 '11 at 1:14

How do you output the formatted phone number?

– aparker81
Nov 26 '12 at 17:08

add a comment |

Thanks, this works well.

– Nietzsche
Dec 24 '11 at 4:54

I would add, don't clean the number before putting into the DB, clean it when you display it to yourself or others. That way you always have the option to show them what they entered.

– Adrian Lynch
Dec 24 '11 at 20:50

1

I store both the phone number as entered and a cleaned up version. I have an index in the DB on the cleaned version so i can search by phone number a lot faster.

– Yisroel
Dec 25 '11 at 1:14

How do you output the formatted phone number?

– aparker81
Nov 26 '12 at 17:08

Thanks, this works well.

– Nietzsche
Dec 24 '11 at 4:54

I would add, don't clean the number before putting into the DB, clean it when you display it to yourself or others. That way you always have the option to show them what they entered.

– Adrian Lynch
Dec 24 '11 at 20:50

I store both the phone number as entered and a cleaned up version. I have an index in the DB on the cleaned version so i can search by phone number a lot faster.

– Yisroel
Dec 25 '11 at 1:14

How do you output the formatted phone number?

– aparker81
Nov 26 '12 at 17:08

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu