How to normalize phone number in the column in SQL using PHP?

Question

I have a table in the SQL database with a phone number column which contains a lot of numbers. The numbers look like those:

350-564-3210 350.564.3210 (350) 564-3210

These numbers are not normalized. I want to normalize with format like this:

+13505643210

It includes +1 as prefix for country code.

use a replace function. Replace '.', '-', ' ', '(', ')' with ''. and prepend +1 to it. Do some diging on this — Praneet Nadkar, Oct 01 '18 at 05:28
Be careful about trying to automate this. If "not normalized" means "they are all ten-digit US phone numbers with no country codes, but with varying punctuation," removing non-numeric characters and prepending +1 would work (note that none of the solutions offered here so far actually do this; look into regex replace unless you're absolutely certain of the subset of characters you need to remove). For any other conditions, none of these things will help you; phone numbers are notoriously difficult to normalize. — kungphu, Oct 01 '18 at 05:52
@kungphu country prefix normalization is always 00, so I use opinion as +1 — Ivan, Oct 01 '18 at 05:56

Kedar Limaye · Accepted Answer · 2018-10-01T06:07:08.970

2

First of all show us any query you did, as far as i am concerned what do you mean by normalization?

You need a function which does- replace your special characters and then append +1 to left of string

You can replace and concat like this

select concat('+1',replace('350-564-3210','-',''))

But this for one phone number you will need to make a generalize function

edited Oct 01 '18 at 06:07

answered Oct 01 '18 at 05:40

Kedar Limaye

Isn't possible to select ALL numbers from the column before to replace? – Ivan Oct 01 '18 at 05:54
See @fa06 answer, what's performance difference between `replace()` with multiple `replace();` inside and `REGEXP_REPLACE()` ? – Ivan Oct 01 '18 at 06:27
@Ivan its regular expression replace-https://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql – Kedar Limaye Oct 01 '18 at 06:29
2

yes may be not diiference ,but its quite usefull instead of using multiple replace functions .But it is only available for mysql 8.0+ – Kedar Limaye Oct 01 '18 at 06:32
1

Most sites use mySql 5.6+ including wordpress site, so your answer seems useful after all – Ivan Oct 01 '18 at 06:35

score 1 · Answer 2 · answered Oct 01 '18 at 05:37

1

Store special characters in an array . Do a string replace and concatenate +1. Try the following

$string = "350-564.3210";
$arrayString = array('-', '.', ')', '(', ' ');
$result = str_replace($arrayString, '', $string);
print_r('+1'.$result);

answered Oct 01 '18 at 05:37

Roshni hegde

score 1 · Answer 3 · answered Oct 01 '18 at 05:58

1

Try with REGEXP_REPLACE()

SELECT concat('+1',REGEXP_REPLACE('(350).564.3210','[-.()]','',1,0))

answered Oct 01 '18 at 05:58

Fahmi

Since I don't want to post an answer here (I doubt the reality of this situation is simple enough for this approach), why are you not just replacing all non-numeric digits? Either `[^\d]+` or whatever format PHP's regex engine supports would be a lot more appropriate here. – kungphu Oct 01 '18 at 06:00
yes, that could an another option definitely @kungphu – Fahmi Oct 01 '18 at 06:01
See @Kedar Limaye answer, what's performance difference between `replace()` with multiple `replace();` inside and `REGEXP_REPLACE()` ? – Ivan Oct 01 '18 at 06:29

3 Answers3