Scramble a column in SQL Server?

Question

We have a web app we'd like to demo to prospects, but our best way of doing so is with existing data, for a full experience. Certainly, we don't want to do this with actual customer names or addresses, etc visible in the app. Is there an easy way in SQL Server to randomize or scramble a varchar or text field?

None of these columns are keys in anyway, either primary or foreign.

Answer 1

This is a late answer but I was not satisfied with any internet search on this matter. Here's an example that will shuffle the firstname and lastname in a customers table to create new names:

--Replace Customers with your table name
select * from Customers

--Be sure int match your id column datatype
Declare @id int

--Add a WHERE here to select just a subset of your table
DECLARE mycursor CURSOR FOR SELECT id FROM Customers
OPEN mycursor
FETCH NEXT FROM mycursor INTO @id;

WHILE (@@FETCH_STATUS = 0) 
BEGIN
    --We loop
    --Warning: NEWID() is generated once per query, so we update the fullname in two queries.
    UPDATE Customers
        SET FirstName = (SELECT TOP 1 FirstName FROM Customers ORDER BY NEWID())
    WHERE id = @id
    UPDATE Customers
        SET LastName = (SELECT TOP 1 LastName FROM Customers ORDER BY NEWID())
    WHERE id = @id
    FETCH NEXT FROM mycursor INTO @id;
END

CLOSE mycursor;
DEALLOCATE mycursor;

select * from Customers