Scramble a column in SQL Server?

Question

We have a web application that we would like to demonstrate to potential customers, but our best way to do this is with existing data for a complete experience. Of course, we don’t want to do this with actual customer names or addresses, etc. Visible in the application. Is there an easy way in SQL Server to randomize or scramble a varchar or text field?

None of these columns is in any case a key, either primary or foreign.

Answer 1

This is a late answer, but I was not satisfied with any online search on this. Here is an example that will shuffle the first and last name in the client 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

Answer 2

Redgate has a tool for this: http://www.red-gate.com/products/SQL_Data_Generator/index.htm

Do not use it, but redgate tools are very good.

EDIT

It generates data, not scrambles, but it can come in handy.

Answer 3

, . , " ", o, m l, e a, s t, t rr,

Moke Smoth
Loke Sloth
Loka Sloth
Loka Tloth
Loka Rrlorrh

, , , ( , , ). .

Answer 4

- .

- , - , , , (CLR T-SQL), , , , , .

, Red Gate, , Visual Studio Team, , , Integration Services. , .

Answer 5

dbForge : http://www.devart.com/dbforge/sql/data-generator/

Answer 6

, :

use master;

declare @length as  int = 50;   --acts as maximum length for random length expressions
declare @rows   as  int = 10;

SELECT 
    CONVERT( VARCHAR(max), crypt_gen_random( @length     ))                                 as  FixedLengthText
,   CONVERT(NVARCHAR(max), crypt_gen_random( @length * 2 ))                                 as  FixedLengthUnicode
,   (   select crypt_gen_random((@length/8*6)) 
        where value."type" is not null  --refer to outer query, to get different value for each row
        FOR XML PATH(''))                                                                   as  FixedLengthBase64
,   CONVERT( VARCHAR(max), crypt_gen_random( (ABS(CHECKSUM(NewId())) %  @length     )+1 ))  as  RandomLengthText
,   CONVERT(NVARCHAR(max), crypt_gen_random( (ABS(CHECKSUM(NewId())) % (@length * 2))+1 ))  as  RandomLengthUnicode
,   (   select crypt_gen_random( ( (ABS(CHECKSUM(NewId())) % @length )+1 )/8*6 )
        where value."type" is not null  --refer to outer query, to get different value for each row
        FOR XML PATH(''))                                                                   as  RandomLengthBase64
FROM  dbo.spt_values  AS  value
WHERE   value."type" = 'P'  --Limit "number" to integers between 0-2047
    and value.number <= @rows
;

Answer 7

, , sql, - . , ( ), .

if exists (select 1 where object_id('tempdb..#columnsToUpdate') is not null)
begin
    drop table #columnsToUpdate
end
create table #columnsToUpdate(tableName varchar(max), columnName varchar(max), max_length int)

if exists (select 1 where object_id('fnGetSanitizedName') is not null)
begin 
    drop function fnGetSanitizedName
end

if exists (select 1 where object_id('random') is not null)
begin 
    drop view random
end

if exists (select 1 where object_id('randUniform') is not null)
begin 
    drop function randUniform
end

GO

create view random(value) as select rand();
go

create function dbo.randUniform() returns real
begin
    declare @v real
    set @v = (select value from random)
    return @v
end

go

CREATE FUNCTION dbo.fnGetSanitizedName 
(
    @functionName nvarchar(max),
    @length int
)
RETURNS varchar(max)
AS
BEGIN
    return left(SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('SHA1', cast(cast(cast(dbo.randUniform() * 10000 as int) as varchar(8)) as varchar(40)) + @functionName)), 3, 32), @length)

END
GO

begin transaction
set nocount on

insert into #columnsToUpdate
select tables.name, columns.name, 
    case
        when types.name = 'nvarchar' then columns.max_length / 2
        else columns.max_length
    end as max_length
from sys.tables tables
inner join sys.columns columns on tables.object_id=columns.object_id
inner join sys.types types on columns.system_type_id = types.system_type_id
where types.name in ('nvarchar', 'varchar')


declare @tableName varchar(max)
declare @columnName varchar(max)
declare @length int
declare @executingSql varchar(max)

declare tableUpdateCursor cursor 
    for select tableName, columnName, max_length from #columnsToUpdate
open tableUpdateCursor

    fetch next from tableUpdateCursor into @tableName, @columnName, @length
    while @@fetch_status = 0
    begin
        set @executingSql = 'update ' + @tableName + ' set ' + @columnName + ' = dbo.fnGetSanitizedName(' + @columnName + ',' + cast(@length as varchar(max)) + ')'
        print @executingSql
        exec(@executingSql)

        fetch next from tableUpdateCursor into @tableName, @columnName, @length
    end


close tableUpdateCursor
deallocate tableUpdateCursor

set nocount off

rollback -- Can remove the rollback when you are sure about what your are doing.

drop table #columnsToUpdate
drop function dbo.fnGetSanitizedName
drop view random
drop function randUniform