MySQL - How to replace a string with a random string from a list

Question

I know how to replace a string using a query like:

UPDATE  `postmeta` SET  `meta_value` = REPLACE(`meta_value`, 'old_string', 'replacement')

What I want to do is for Mysql to choose a random string from a list of strings, sort of like this:

UPDATE  `postmeta` SET  `meta_value` = REPLACE(`meta_value`, 'old_string', RAND(['replacement','replacement2','replacement3']))

OR

I would like for it to cycle through the list instead of choose them randomly... eg: Replace the first record with 'replacement', second record with 'replacement2', fourth record with 'replacement'...

Is this sort of query possible?

Alexander van Oostenrijk · Accepted Answer · 2019-12-27 16:01:59Z

2

You can use MySQL's ELT function. ELT() returns the N th element of a list of strings: str1 if N = 1, str2 if N = 2, and so on:

UPDATE `postmeta` 
SET `meta_value` = REPLACE(`meta_value`, 'old_string', 
  ELT(1 + RAND() * 3, 'replacement', 'replacement2', 'replacement3'))

edited Dec 27, 2019 at 16:01

answered Dec 27, 2019 at 15:55

Alexander van Oostenrijk

4,7843 gold badges25 silver badges37 bronze badges

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

2 Comments

jinyus Over a year ago

Alright this is great but while writing the question, I had another idea. Say the query will update 200 rows. Is there any way to get the number of the row being currently updated? I would be able to cycle through the list instead of randomly selecting using (current_iteration % length_of_list +1) in ELT.

Alexander van Oostenrijk Over a year ago

That's trickier. I would suggest reading this article for a few pointers.

GMB · Accepted Answer · 2019-12-27 15:54:15Z

1

You could use rand() and a case expression:

update `postmeta` 
set `meta_value` = replace(
    `meta_value`, 
    'old_string',
    case floor(1 + rand() * 2) 
        when 1 then 'replacement'
        when 2 then 'replacement2'
        when 3 then 'replacement3'
    end
);

floor(1 + rand() * 2) generates a random value between 1 and 3 (see the documentation for more details).

answered Dec 27, 2019 at 15:54

GMB

224k25 gold badges103 silver badges151 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2019-12-27 15:55:39Z

1

Use can use the elt() function and rand():

UPDATE postmeta
    SET meta_value = REPLACE(meta_value, 'old_string', 
                             ELT(FLOOR(RAND() * 3 + 1, 'replacement', 'replacement2', 'replacement3')
                            );

If you don't want to actually replace part of the string, but want to assign the whole string, then I think you want:

UPDATE postmeta
    SET meta_value = ELT(FLOOR(RAND() * 3 + 1, 'replacement', 'replacement2', 'replacement3')
    WHERE meta_value = 'old_string';

answered Dec 27, 2019 at 15:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Parth prajapati · Accepted Answer · 2019-12-27 16:21:09Z

0

//you could use ran() for example of my own:
CREATE TABLE RandomFirstNames (
    id SMALLINT UNSIGNED AUTO_INCREMENT,
    first_name VARCHAR(...),
    PRIMARY KEY(id) )
SELECT first_name FROM FirstNames ORDER BY RAND();

answered Dec 27, 2019 at 16:21

Parth prajapati

622 bronze badges

Collectives™ on Stack Overflow

MySQL - How to replace a string with a random string from a list

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related