Replace in SQL - Character to keep a piece of text

Question

Replace in SQL - Character to keep a piece of text

Navigation

#1 by (6 votes)
#2 by (5 votes)

7

I need to modify a part in the text of a table. The records for an email are [email protected] and need to change all 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').

I do not really know which wildcard character to keep the user's acronym in (coming before @).

sql string

asked by anonymous 19.08.2014 / 17:08

2 answers

How to get the last record entered in SQL according to your date How to get multiple groups in a Regex?

score 6 · Answer 1

In MySQL a possible solution would be to search through REGEXP for all emails ending in @jf.gov.br .

UPDATE  Tabela
SET     email = REPLACE(email, '@jf.gov.br', '@jf.jus.br')
WHERE   email REGEXP '@jf.gov.br$';

The character $ is the end-of-string marker.

Functional example in SQL Fiddle .

Source : SOen - Updating email addresses in MySQL (regexp?)

score 5 · Answer 2

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

CREATE TABLE tabela(email VARCHAR(320));

INSERT INTO tabela VALUES ('[email protected]');
INSERT INTO tabela VALUES ('[email protected]');

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

SQLFiddle

Note that you do not even need WHERE because replace will only replace if found.

But if you want you can put in order to avoid " pranks " 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;

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