Include a digit before the phone in the DB [duplicate]

Posted on

Question :

I have a system where 18 thousand users have been registered, but I realized that cell phones and landlines are missing a digit. Ex: (21) 9999-999 or (21) 2222-222. Although the field is CHAR (14). Can you directly include the number 9 (in the case of the cell phone) and the number 2 (in the case of the fixed) in all the registers at one time? It can be in php or directly in Mysql.


Answer :

Try this code:


UPDATE SET phones tel = REPLACE (tel, ‘(21)’, ‘(21) 9’);

The REPLACE method replaces one string with another, in which case it will replace one part of the string with another that contains the 9th digit.

However if the phones are all the same I would recommend this code:


UPTADE SET phones tel = REPLACE (tel, ‘(21) 999999-999’, ‘(21) 99999-9999’);


I do not know if it will work with MySQL, but in the sql server I would do it as follows.

declare @Celular varchar(14) = '(21)9999-999'

declare @NovoCelular varchar(14) 

set @NovoCelular = case when LEN(@Celular) = 12   --  LEN = LENGTH no mysql
    then  replace((LEFT(@Celular, 4) + '9' + RIGHT(@Celular, LEN(@Celular) - 4)),'-', '' )
    else @Celular

set @Celular = LEFT(@NovoCelular, 8) + '-' + RIGHT(@NovoCelular, LEN(@NovoCelular) - 8)

select @Celular

It’s an idea how to do it, just mount your query with the necessary filters.


Leave a Reply

Your email address will not be published. Required fields are marked *