8
I need to modify a part in the text of a table. The records of an email are [email protected] and I need to change everyone to jf.jus.br. Only the first part of the email doesn’t change (the xxx).
I can make a
update
TABELA
set
email = REPLACE (email, '%.jf.gov.br', '%jf.jus.br').
I don’t really know what the joker character to keep the user’s acronym (which comes before the @).
10
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;
Note that the WHERE replace will only replace if it finds.
But if you want you can put in order to avoid "prank" may make:
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;
will be generic and will probably work in most databases. However Oracle 11g would be an example of why, in the same instead of RIGHT(email,9) should use substr(email,-9).
The problem of making a update without the WHERE, although it is something artifical, is to have a list or user of type [email protected], with the WHERE RIGHT or REGEXP $ We’ll eliminate any pranks like that.
@Anthonyaccioly I agree, but I do not believe that there will be such a case. And I made sure to show the where to cover this (should the PO consider it necessary).
6
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 marker of the string.
Functional example in SQL Fiddle.
Browser other questions tagged sql string
You are not signed in. Login or sign up in order to post.
Hello Josie, what is your database (Mysql, SQL Server, Oracle, etc)? If possible update the tags including the specific database.
– Anthony Accioly