Replacing multiple strings with the same string replacement

Question

Been looking all over for this and I don't think nested replace is the answer. I have a list of email address that I need to keep unique, but I need to make them all fake for testing. So my idea was to just replace the '.com', '.net', '.org' and so on to '.mydomain.com'. But there are a LOT of endings in total.

I realized I could just remove the @ and add '@mydomain.com' to the end, but now I also want to figure out how to solve this particular problem.

Instead of doing:

BEGIN TRANSACTION; 
UPDATE Customer
  SET Email=REPLACE(Email, '.com','.mydomain.com')
where email not like '%.mydomain.com%'
COMMIT TRANSACTION;

for each case '.com', '.net', '.org'........

is there a way to say, replace all of these ('.com', '.net', '.org') with '.mydomain.com' in one statement?

something like this.

BEGIN TRANSACTION; 
UPDATE Customer
  SET Email=REPLACE(Email, (in ('.com', '.net', '.org')),'.mydomain.com')
where email not like '%.mydomain.com%'
COMMIT TRANSACTION;

Bohemian · Accepted Answer · 2020-05-14 23:19:39Z

1

Following the KISS principle, just run 3 separate queries.

update Customer set email = replace(email, '.com', '.mydomain.com') where email not like '%.mydomain.com%';
update Customer set email = replace(email, '.net', '.mydomain.com') where email not like '%.mydomain.com%';
update Customer set email = replace(email, '.org', '.mydomain.com') where email not like '%.mydomain.com%';

answered May 14, 2020 at 23:19

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Carlf Over a year ago

I was hoping there was some simple function I wasn't aware of, but since there isn't I agree with this. And using excel it's easy to create an update script for reach of the 100+ endings. Thanks!

Bohemian Over a year ago

NP. See acceptIng answers

Wazted · Accepted Answer · 2020-05-14 23:33:24Z

0

I think something like this should work.

UPDATE Customer
  SET Email (case 
      where CHARINDEX(Email, '.com') > 0 then REPLACE(Email, '.com','.mydomain.com')
      where CHARINDEX(Email, '.net') > 0 then REPLACE(Email, '.net','.mydomain.com')
      where CHARINDEX(Email, '.org') > 0 then REPLACE(Email, '.org','.mydomain.com')
      end)
where email not like '%.mydomain.com%'

May CHARINDEX can be replaced by any function that verify if a string contain a substring.

answered May 14, 2020 at 23:33

Wazted

1011 silver badge10 bronze badges

Comments

sticky bit · Accepted Answer · 2020-05-14 23:41:37Z

0

You can use an UPDATE ... FROM to join a derived table of the TLDs you want to replace. (You can also join a "real" table with the TLDs if you have one.)

UPDATE c
       SET c.email = replace(c.email, concat('.', tld.tld), '.mydomain.com')
       FROM customer c
            INNER JOIN (VALUES ('com'),
                               ('org'),
                               ('net')) tld (tld)
                       ON c.email LIKE concat('%.', tld.tld)
       WHERE c.email NOT LIKE '%.mydomain.com';

db<>fiddle

It won't solve the problem though that, if a substring that matches a TLD is in the string somewhere else as at the end, this substring also gets replaced. But probably that's not an issue here.

edited May 14, 2020 at 23:41

answered May 14, 2020 at 23:36

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Comments

Tarik · Accepted Answer · 2020-05-14 23:42:35Z

0

Another solution is to have nested replace statements:

update Customer set email = replace(replace(email, '.com', '.mydomain.com'), '.net', '.mydomain.com')

You can go on to as many levels as you want. You can even generate the replace statement dynamically.

Another possible solution is to use a stored procedure.

edited May 14, 2020 at 23:42

answered May 14, 2020 at 23:34

Tarik

11.2k2 gold badges31 silver badges47 bronze badges

1 Comment

sticky bit Over a year ago

Quote from the OP: "... I don't think nested replace is the answer."

Collectives™ on Stack Overflow

Replacing multiple strings with the same string replacement

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related