CODEWITHSUNDEEP
mysqlsqldatabase
jinyus
jinyus

Reputation: 566

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

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?

Upvotes: 0

Views: 1061

Answers (4)

Parth prajapati
Parth prajapati

Reputation: 62

//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();

Upvotes: 0

Alexander van Oostenrijk
Alexander van Oostenrijk

Reputation: 4764

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'))

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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';

Upvotes: 1

GMB
GMB

Reputation: 222462

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).

Upvotes: 1

Related Questions