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

[steviechan83]

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

[effigy]

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]

[lead2gold]

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.

[Ninjakreborn]

[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

[steviechan83]

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

[HeyRay2]

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.

[steviechan83]

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 :)

[lead2gold]

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...

[effigy]

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]