Replace in SQL - Character to keep a part of the text



  • I need to modify a part in the text of a table. The records of an email are xxx@jf. Gov.br and need to change everyone to jf.jus.br. Only the first part of the email does not change (the xxx).

    I can do one

    update 
      TABELA 
    set 
      email = REPLACE (email, '%.jf.gov.br', '%jf.jus.br').
    

    Actually I don't know what the Joker character to keep the user acronym full (which comes before @).



  • PostgreSQL; MS SQL Server 2012; Oracle 11g; MySQL

    CREATE TABLE tabela(email VARCHAR(320));
    

    INSERT INTO tabela VALUES ('eu@jf.gov.br');
    INSERT INTO tabela VALUES ('eula@jf.gov.br');

    UPDATE
    tabela
    SET
    email = replace(email, 'jf.gov.br', 'jf.jus.br');
    SELECT email FROM tabela;

    http://sqlfiddle.com/#!15/c212f

    Note that it is even necessary WHERE because the replace will only replace if you find.

    But if you want you can put it in order to avoid https://pt.stackoverflow.com/questions/29508/replace-no-sql-caractere-para-manter-uma-parte-do-texto/29524#comment54938_29524 you can do:

    UPDATE
    tabela
    SET
    email = replace(email, 'jf.gov.br', 'jf.jus.br');
    SELECT email FROM tabela;
    WHERE RIGHT(email,9) = 'jf.gov.br';
    SELECT email FROM tabela;

    It will be generic and probably work in most databases. However Oracle 11g would be an example of exception because, in the same instead of RIGHT(email,9) should use substr(email,-9).




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2