Format Phone Numbers to NNN-NN-NNNN

[Rich Hartnett]

I have about 8,000 phone numbers in a table. Some were entered in this format "301-555-1212", and some are like this "3015551212." I'd like to correct them all to the format of "301-555-1212," so I tried running an operation of:
left(Phone,3) + "-" + substr(Phone,4,3) + "-" + substr(Phone,7,4)

That works if the numbers were entered in the format of "3015551212," but they are already in the correct format of "301-555-1212," it causes them to change to this: "301--55-5-12,"

Is there a way to run the operation and only have it effect numbers that were entered incorrectly?

[madtowng]

Hi Rich,

Try stuff(stuff(remspecial(phone),7,0,"-"),4,0,"-")
It will also do the job for the people the like to use periods and/or spaces.

[Rich Hartnett]

Thanks Madtowng. I tried that expression, but it took 3015551212 and turned it into 301555-1212

[madtowng]

Looks like it's missing one of the stuff functions.

Here's a more complete answer, as well as several tries.
Capture.png

function phoneFormatter as C (pn as C)
    return  stuff(stuff(remspecial(pn),7,0,"-"),4,0,"-")
end function​

[Ted Giles]

Just a thought.
if you remove all previous formatting and then apply your code it should work Back it up first!

[madtowng]

Ted Giles It's easy to see you looked at the code.

[Rich Hartnett]

That was it, Greg. The extra command "Stuff" at the beginning of the expression was the issue and now it works great!

Thanks everyone.

[madtowng]

Glad to help Rich.

[Ted Giles]

Nice bit of code Gregg.
Are you taking over from Stan?
I just popped a comment from my phone as a Q&D solution.
Apologies I didn't pay more attention. (But it would have worked)

[madtowng]

I'd love to say yes, but until I solve my text overload issue I think it's
best I stick with things I understand.

[Ted Giles]

LOL!
I have tried dictating stuff via my car to the forum ( it has voice recognition and voice acivation and we have conversations) to save texting. Boy, have I had issues!
"Sorry officer!"

[Al Buchholz]

Yes.

Undo any formatting with remspecial() then apply the formatting you want.

You should check the length of the data with len(alltrim())

Did you intend for your title to be NNN-NN-NNNN or NNN-NNN-NNNN?

[Pat Bremkamp]

We have about 15,000 phone numbers in our tables and we are constantly getting additions and updates from our clients in csv and excel, so we found it is faster to do the check in MySQL. We also need the option for adding a leading 1 and/or dashes. If anybody is interested, here is a Stored Function that will handle that

Input parameters are pPhoneIn varchar(24), pLeading1 TINYINT(1), pDashes TINYINT(1)

BEGIN
/* Function FixPhone
This routine strips all non numeric characters from a number and optionally inserts our standard characters
Setting pLeading1 = 1 results in leading 1. setting pDashes = 1 results in dash separators.
Otherwise just the number. Includes error checking.
If the number cannot be converted, it is kept as is. */

DECLARE vPhoneIn, vPhoneWork, vPhoneOut, vResult VARCHAR(24);
DECLARE vLatinNumber VARCHAR(24);
DECLARE vCharLength, vCount TINYINT(3) UNSIGNED;
DECLARE vThisChar, vLeadingChar CHAR(1);
DECLARE flag_done, flag_error TINYINT(1) UNSIGNED;
# Initialize
SET flag_done = 0, flag_error = 0, vLeadingChar = '';
SET vPhoneIn = TRIM(pPhoneIn);
SET vPhoneOut ='', vPhoneWork = '';
# Handle NULL and blank
IF ISNULL(vPhoneIn) THEN
SET vPhoneOut = NULL;
SET flag_done = 1;
ELSEIF vPhoneIn = '' THEN
SET vPhoneOut = '';
SET flag_done = 1;
END IF;
# Convert to latin1 colation to get accurate count of number of characters
IF flag_done = 0 THEN
SET vLatinNumber = CONVERT(vPhoneIn USING latin1);
SET vCharLength = CHAR_LENGTH(vLatinNumber);
# strip out any non numeric characters
SET vCount = 1;
WHILE vCount <= vCharLength DO
SET vThisChar = MID(vLatinNumber,vCount,1);
IF vThisChar IN ('1','2','3','4','5','6','7','8','9','0') THEN
SET vPhoneWork = concat(vPhoneWork,vThisChar);
END IF;
SET vCount = vCount + 1;
END WHILE;
SET vCharLength = CHAR_LENGTH(vPhoneWork);
SET vLeadingChar = LEFT(vPhoneWork,1);

# Check for leading 1
IF pLeading1 = 0 THEN
IF vCharLength = 10 AND vLeadingChar != '1' THEN
SET vPhoneOut = vPhoneWork;
ELSEIF vCharLength = 11 AND vLeadingChar = '1' THEN
SET vPhoneOut = RIGHT(vPhoneWork,10);
ELSE
SET flag_error = 1;
SET vPhoneOut = pPhoneIn;
END IF;
ELSEIF pLeading1 = 1 THEN
IF vCharLength = 11 AND vLeadingChar = '1' THEN
SET vPhoneOut = vPhoneWork;
ELSEIF vCharLength = 10 AND vLeadingChar <> '1' THEN
SET vPhoneOut = CONCAT('1',vPhoneWork);
ELSE
SET flag_error = 1;
SET vPhoneOut = pPhoneIn;
END IF;
END IF;

# Insert dashes if needed
IF flag_error = 0 AND pDashes = 1 THEN
IF LEFT(vPhoneOut,1) = '1' THEN
SET vPhoneOut = CONCAT(LEFT(vPhoneOut,4),"-",MID(vPhoneOut,5,3),"-",RIGHT(vPhoneOut,4));
ELSE
SET vPhoneOut = CONCAT(LEFT(vPhoneOut,3),"-",MID(vPhoneOut,4,3),"-",RIGHT(vPhoneOut,4));
END IF;
END IF;
END IF;
SET vResult = vPhoneOut;
RETURN vResult;
END

[madtowng]

Pat Bremkamp How did you get the data from spreadsheets into mysql ?

[Ted Giles]

Isn't it the simple Import/Export function?