Jump to content

Archived

This topic is now archived and is closed to further replies.

steviechan83

How to eliminate words (a-z) in a phone field?

Recommended Posts

Hi

I have a field phoneNum and it contains phone numbers in all sort of formats.

xxx-xxx-xxxx
(xxx)-xxx-xxxx
Branch: xxx-xxxx
Plant: xxx-xxxx
xxx-xxxx/?

I would like to change all the phone number to a standard format to XXX-XXX-XXXX.

Could anyone suggest an approach or show me an example? is there some way i can preprocess the data and then store it into another column?

I've thought of using regex or using LIKE CONCAT()?


Also, i'm using PHP 5 and mySQL 4.0.15

Thanks

Stephen

Share this post


Link to post
Share on other sites
Here's something to start with, you could add much more:

[code]
<pre>
<?php

$tests = array(
'123-456-7890',
'(123)-456-7890',
'Branch: 123-4567',
'Plant: 987-6543',
'123-4567/?',
);

$format = '%20s';

foreach ($tests as $test) {
### Original.
printf ($format, "$test => ");
### Only allow digits, hyphens, and parens.
$test = preg_replace('/[^-\d()]/', '', $test);
printf ($format, "$test => ");
### Format.
$test = preg_replace('/
^        ### BOL
(\()?    ### Possible opening paren.
(\d{3})  ### 3 digits.
(?(1)\))  ### Match close paren if there was an open.
-        ### Hyphen.
(\d{3})  ### 3 digits.
-        ### Hyphen.
(\d{4})  ### 4 digits.
$        ### EOL
/x', "$2-$3-$4", $test);
printf ($format, $test);
echo '<br />';
}

?>
</pre>
[/code]

Share this post


Link to post
Share on other sites
here is another aproach:
[code]
$phone_no = "(123)-123-1234";
$phone_no = preg_replace('/[^\d]/', '', $phone_no); // produces 1231231234
$result = substr($phone_no,0,3).'-'. substr($phone_no, 3, 3).'-'.substr($phone_no,6,4);

echo $result; // 123-123-1234
[/code]

It doesn't matter what you pass in, it will strip off anything that isn't a number, keep the first 10 digits and format them to your liking.

Share this post


Link to post
Share on other sites
[code]$regexphone = "
/^[0-9]{3}-[0-9]{3}-[0-9]{4}$/";  // regex phone validation
if (!preg_match("$regexphone", $_POST['phone'])) {
$errorhandler .= "Telephone format invalid, use 000-000-0000 format<br />";
}[/code]
format would always have to be
000-000-0000
or 555-555-5555
like area code- first3 letters- last 4 lettters

Share this post


Link to post
Share on other sites
Thanks for all the suggestion and help. 

I found that the first approach is more flexible than the second approach because the second approach cannot handle 1-800-123-1231 numbers

However, with the first approach when i have numbers like (604)192-12312 with no dash.  The regex would not work.  Also cases were there it is "Jan 19;27" would give me 1927.

I'm not familar with regex but I'm learning :) I was wondering how I should go about this?

I tried another regex to specify whether the separator is either a dash space, or a period but this would not work either

$regex = "/
            \(?     # optional parentheses
              \d{3} # 3 Digit
            \)?     # optional parentheses
            [-\s.]? # separator is either a dash, a space, or a period.
              \d{3} # 3 digit
            [-\s.]  # another separator
              \d{4} # 4 digit
           /x";

Thanks

STephen


Share this post


Link to post
Share on other sites
I see the easiest method as requiring users to enter their phone number in XXX-XXX-XXXX format, and on your HTML form next to your phone input field, note this.

If a user isn't smart enough to input their phone number in the method almost every other website in existence would require, then let them know the right way to do it.

Share this post


Link to post
Share on other sites
Well yes i see that validating the input is necessary which I do have in place now.  But I'm trying to clean up an existing customer database where the phone number field has all sorts of weird formats  :(

Thanks for the suggestion though :)

Share this post


Link to post
Share on other sites
well you could rework this code to your likings:
[code]
$phone_no = "1-(123)-123-1234";
$phone_no = preg_replace('/[^\d]/', '', $phone_no); // produces 11231231234
if(strlen($phone_no) == 10){
  $result = substr($phone_no,0,3).'-'. substr($phone_no, 3, 3).'-'.substr($phone_no,6,4);
}else if(strlen($phone_no) == 11){
  $result = substr($phone_no,1,3).'-'. substr($phone_no, 4, 3).'-'.substr($phone_no,7,4); // strip first #
}else{
  $echo "could not format, output Id #";
}
echo $result; // 123-123-1234
[/code]

I doubt very much you'd have many errors left over, even if you had 10 or so, you could go edit them yourself manually.  If you have alot of cases where there are more then 10, or 11 digits left over you already have a mangled system that would be hard to fix anyways...

Share this post


Link to post
Share on other sites
I was thinking more high-end, but lead2gold's first solution was more applicable for being able to take any data they give you. Below is an extension of that that auto-hyphenates. You might want to add an additional check to make sure the lengths are valid, i.e., 8 for 123-4567, 12 for 123-456-7890, and 14 for 1-800-123-4567.

[code]
<pre>
<?php

$tests = array(
'123-456-7890',
'(123)-456-7890',
'Branch: 123-4567',
'Plant: 987-6543',
'123-4567/?',
'1-800-123-1231',
);

$format = '%20s';

foreach ($tests as $test) {
### Original.
printf ($format, "$test => ");
### Only allow digits.
$test = preg_replace('/\D/', '', $test);
printf ($format, "$test => ");
### Remove the last 4.
$test_end = substr($test, -4);
$test_rest = substr($test, 0, count($test) - 5);
### Hyphenate every set of 3.
$test_rest = preg_replace('/
(?<=\d)              ### There should be a preceding digit...
(?=(?:\d{3})+(?!\d)) ### That is followed by 1 or more sets of
### 3 digits, which is not followed by a
### digit.
/x', '-', $test_rest);
printf ($format, $test_rest);
### Add the tail back with a hyphen.
$test = "$test_rest-$test_end";
printf ($format, $test);
###
echo '<br />';
}

?>
</pre>
[/code]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.