Format phones in mysql table

Asked

Viewed 847 times

0

I have a MYSQL table with the registration of people that phones are formatted this way.

519995150105
5133116284
51991178789
51999862438

What I need is a command to format the phones to look like this:

(51)9995150105
(51)33116284
(51)991178789
(51)999862438
mysql formatting

  • Here you already have an answer https://answall.com/questions/113641/formatr-mascarar-cpf-no-mysql-ou-pdo ... but you can use native substring functions to update, it should be easier.

2 answers

1


If the current formatting is always as you mentioned, it is simple. Just make a replace:

update tabela set telefone = replace(telefone,' 51',' (51)')

Only the first phone would be without formatting, but then you can arrange manually or we can assemble another command.

0

I use the following query to format my numbers.
But mine are stored without the 55.
You can adapt your reality:

select 
  numero, 
  concat('(',substr(numero_cleansed,1,2),') ',substr(numero_cleansed,3,5),'-',substr(numero_cleansed,8)) AS numero_formatted
from (
  select 
    numero, 
    replace(replace(replace(numero,'(',''),')',''),'-','') as numero_cleansed
  from chip where LENGTH(numero)=11
  ) foo

Browser other questions tagged mysql formatting

You are not signed in. Login or sign up in order to post.