How can replace a column of all records with random strings?

Question

I have a table customers_info in my MySQL having a column 'address'.

I would like to replace the values of 'address' in all the rows with random texts (anything, for example, like xwdjduhyrmdz) for the privacy reason.

I found this SQL and tried it on phpmyadmin but didn't work for me.

UPDATE customer_info
SET address = LEFT(REPLACE(CAST(NEWID() AS CHAR(40)), '-', ''), @Characters)

How can I do this ?

You are expected to try to **write the code yourself**. After [doing more research](https://meta.stackoverflow.com/q/261592/1011527) if you have a problem **post what you've tried** with a **clear explanation of what isn't working** and provide [a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). Read [How to Ask a good question](http://stackoverflow.com/help/how-to-ask). Be sure to [take the tour](http://stackoverflow.com/tour) and read [this](https://meta.stackoverflow.com/q/347937/1011527). — Sloan Thrasher, Mar 30 '18 at 12:15
Pretty sure newID is a SQL server function not a mySQL one. Take a look at: https://stackoverflow.com/questions/8207380/how-to-generate-random-chars-and-insert-with-mysql which uses `SUBSTRING(MD5(RAND()) FROM 1 FOR 5)` md5 from 1 for 40 in your case I think. — xQbert, Mar 30 '18 at 12:44
Seems odd to lose address info like this. Are you doing this when making a test database that others can access to aid in privacy? If so you could just randomly shuffle the order so the addresses don't match the right owner; but still look like addresses. Two derived tables order both by rand assign a row number to each and join. Some may match; but not many. — xQbert, Mar 30 '18 at 12:50

score 1 · Accepted Answer · edited Mar 30 '18 at 12:50

This query will update all row of address column to random string of 6 character

UPDATE `yourTable` SET `address` = CONCAT(
    SUBSTRING('abcdefghijklmnopqrstuvwxyz', FLOOR(RAND()*26) + 1, 1),
    SUBSTRING('abcdefghijklmnopqrstuvwxyz', FLOOR(RAND()*26) + 1, 1),
    SUBSTRING('abcdefghijklmnopqrstuvwxyz', FLOOR(RAND()*26) + 1, 1),
    SUBSTRING('abcdefghijklmnopqrstuvwxyz', FLOOR(RAND()*26) + 1, 1),
    SUBSTRING('abcdefghijklmnopqrstuvwxyz', FLOOR(RAND()*26) + 1, 1),
    SUBSTRING('abcdefghijklmnopqrstuvwxyz', FLOOR(RAND()*26) + 1, 1)
);

How can replace a column of all records with random strings?

1 Answers1