Include a digit before the phone in the BD register

Asked

Viewed 217 times

4

I have a system where was registered 18 thousand users, but I realized that the mobile phones and fixed are missing a digit. Ex.: (21)9999-999 or (21)2222-222. Although the field is CHAR(14). Is it possible to include directly the number 9 (in the case of mobile) and the number 2 (in the case of fixed) in all the registrations at once? It can be in php or directly in Mysql.

php mysql

  • Hey, Wallace, almost. rs rs... the case of the other colleague is less serious than mine, because mine is already registered rs rs... I would like to include the digit in the records that have been registered in the database...

  • I think the problem is more serious than I imagined. Using the example of the link that Wallace passed, it was: (21)999999-999. Note that it was uniform.

  • Then you’d have to check how many digits there are before

  • Hello Wallace. Forgive me the abuse, but is that I am not finding a logical reasoning.

2 answers

3


Try this code:

UPDATE phones SET 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 would recommend this code:

UPTADE telefones SET tel = REPLACE(tel, '(21)999999-999', '(21)99999-9999');

0

I don’t know if it will work with Mysql, but in sql server I would do it like this.

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 of how to do, just assemble your query with the necessary filter .

Browser other questions tagged php mysql

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