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.
Include a digit before the phone in the DB [duplicate]
4
asked by anonymous 18.05.2016 / 20:58
2 answers
3
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');
19.05.2016 / 03:26
0
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
end
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.
18.05.2016 / 22:22