MYSQL Replace array of strings with a randomly chosen array of strings

Question

I have a users table with a user_email field. I'd like to replace all user emails that match:

*@a.com
*@b.com
*@c.com

With a randomly chosen string from

d.com
e.com
f.com
g.com

Example:

Before:

--------------------------------------
|user_name | user_email | user_phone |
--------------------------------------
| greg     | [email protected] | 12345      |
--------------------------------------
| harry    | [email protected] | 12345      |
--------------------------------------
| mary     | [email protected] | 12345      |
--------------------------------------
| john     | [email protected] | 12345      |
--------------------------------------
| fred     | [email protected] | 12345      |
--------------------------------------
| alfred   | [email protected]   | 12345      |
--------------------------------------

After (Replace all "a.com", "b.com", "c.com" occurrences with a randomly chosen one from ["d.com", "e.com", "f.com", "g.com"]:

--------------------------------------
|user_name | user_email | user_phone |
--------------------------------------
| greg     | [email protected] | 12345      |
--------------------------------------
| harry    | [email protected] | 12345      |
--------------------------------------
| mary     | [email protected] | 12345      |
--------------------------------------
| john     | [email protected] | 12345      |
--------------------------------------
| fred     | [email protected] | 12345      |
--------------------------------------
| alfred   | [email protected]   | 12345      |
--------------------------------------

John, and Fred remain unchanged. Greg, Harry, Mary, and Alfred get one from ["d.com", "e.com", "f.com", "g.com"] randomly.

Any pointers on how to do this? Thank you

Could you demonstrate exactly what you require as it is unclear what you require — david strachan
– david strachan, Commented Jun 30, 2013 at 20:58
You can create a temporary table, fill in your data. Then create an UPDATE ... SELECT query, that will change every email with a random record from the temporary table. — user4035
– user4035, Commented Jun 30, 2013 at 20:58
I added an explanation there. I can't create a new table, but it isn't a requirement that I use only 1 query. — Alain Jacomet Forte
– Alain Jacomet Forte, Commented Jun 30, 2013 at 21:08
the randomly chosen domain names are just a few, or can be a lot of records? — fthiella
– fthiella, Commented Jun 30, 2013 at 21:09

fthiella · Accepted Answer · 2013-06-30 21:24:29Z

2

If you are looking for a quick and dirty solution, you could use something like this:

UPDATE
  emails
SET
  emails.address = CONCAT(SUBSTRING_INDEX(emails.address, '@', 1), '@', ELT(1+rand()*3, 'd.com', 'e.com', 'f.com', 'g.com'))
WHERE
  SUBSTRING_INDEX(address, '@', -1) IN ('a.com', 'b.com', 'c.com')

Please see fiddle here.

Using SUBSTRING_INDEX(address, '@', -1) you can get the domain part of the address, and you can check if it is a.com, b.com, c.com, etc.

Then you can update your email address, concatenating the first part of the address SUBSTRING_INDEX(address, '@', -1), the separator @, and a randomly chosen domain using ETL function:

ELT(1+rand()*3, 'd.com', 'e.com', 'f.com', 'g.com')

where 1+3 is the number of elements.

edited Jun 30, 2013 at 21:24

answered Jun 30, 2013 at 21:08

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

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

Comments

sailingthoms · Accepted Answer · 2013-06-30 21:42:02Z

0

My first Idea is something like

UPDATE user
SET user_email = CASE
           WHEN user_email LIKE '%a.com' THEN (SELECT REPLACE(user_email, 'a.com', 'd.com'))
           ELSE user_email
END

or to match multiple domains:

UPDATE email
SET user_email = CASE
   WHEN (user_email REGEXP '[a-c].com$') 
   THEN (SELECT CONCAT((SELECT SUBSTRING_INDEX(user_email, '@', 1)), 'y.com' ))
   ELSE user_email
END

edited Jun 30, 2013 at 21:42

answered Jun 30, 2013 at 21:09

sailingthoms

94810 silver badges22 bronze badges

1 Comment

Alain Jacomet Forte Over a year ago

This works. I could just do that for each a.com, b.com, etc. It isn't random as the question requests, but as I explained in the comments I can't create a temporary table for the job.

Collectives™ on Stack Overflow

MYSQL Replace array of strings with a randomly chosen array of strings

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related