i would like to perform Mysql search & replace with random characters, taken from a list. I cannot use regex, since my version is way prior to 8. instead of the below,

i would like to change for instance the letter u with one out of (a,e,i,f,k) randomly.

UPDATE products 
SET 
    productDescription = REPLACE(productDescription,
        'abuot',
        'about');

Is there a mysql command for this task? Actually my goal is to get in the lastnames column, new names that are not exactly like the real ones, so one could work on "anonymous" data.

I would like to replace all rows in a certain column. Say in table products, in column description, we have data like:

abcud
ieruie
kjuklkllu
uiervfd

With the replace function, we would not want to create something like: replace e with i, but replace e with one of (a,e,i,f,k)

example desired output:

abced
ierfie
kjiklkllk
aiervfd

like i said, we plan to use this into last names, we plan to replace many characters with random ones from a list, in an effort to create anonymous data in the column that contains last names.

On a next step, i would like to do the same, in order to make anonymous telephone numbers.

example

726456273
827364878
347823472

replace 3 with one of 0-9,

output:

726456279
827664878
547821472

CodePudding user response：

SELECT REPLACE('product abuot Description', SUBSTRING('product abuot Description', CHARINDEX('abuot', 'product abuot Description') ,5) , 'about')

CodePudding user response：

CREATE FUNCTION smart_replace ( argument TEXT, 
                                search_for CHAR(1),
                                replace_with TEXT )
RETURNS TEXT
NO SQL
BEGIN
SET argument = REPLACE(argument, search_for, CHAR(0));
REPEAT
    SET argument = CONCAT( SUBSTRING_INDEX(argument, CHAR(0), 1),
                           SUBSTRING(replace_with FROM CEIL(RAND() * LENGTH(replace_with)) FOR 1),
                           SUBSTRING(argument FROM 2   LENGTH(SUBSTRING_INDEX(argument, CHAR(0), 1))));
UNTIL NOT LOCATE(CHAR(0), argument) END REPEAT;
RETURN argument;
END

replace e with one of (a,e,i,f,k)

SELECT smart_replace(table.column, 'e', 'aeifk')

replace 3 with one of 0-9

SELECT smart_replace(table.phone, 'e', '0123456789')