DataScramble - Randomizing data rows

: Joel Lipman; Transact-SQL; 07 December 2012; Hits: 7445

What?
Along with my DataJumble function and DataTumble procedure which also help scramble database tables sent to suppliers/developers, this is a function which simply finds random characters and inserts these.

I would recommend using the DataTumble script over this one as this leaves data very difficult to work with:

copy raw

Before:
           StudentID   StudentName          DateOfBirth
           ----------- -------------------- -------------
           1           John Smith           1990-03-21
           2           Fred Bloggs          1988-11-02
           3           Another User         1985-07-11
           4           Yet Another User     1977-06-25

       After:
           StudentID   StudentName          DateOfBirth
           ----------- -------------------- -------------
           1           PDUHjRWJcb           1926-01-02
           2           WRmNqQKxvuV          1969-03-14
           3           nBCkAVDrvdhe         1968-05-05
           4           RJDsFMaeNcLrcMWw     1964-08-08

Before:
StudentID StudentName DateOfBirth
----------- -------------------- -------------
1 John Smith 1990-03-21
2 Fred Bloggs 1988-11-02
3 Another User 1985-07-11
4 Yet Another User 1977-06-25
After:
StudentID StudentName DateOfBirth
----------- -------------------- -------------
1 PDUHjRWJcb 1926-01-02
2 WRmNqQKxvuV 1969-03-14
3 nBCkAVDrvdhe 1968-05-05
4 RJDsFMaeNcLrcMWw 1964-08-08

How?
Precursor
You will need to be able to create the following view to generate random numbers on SQL Server.

copy raw

-- Drop the view if it already exists
IF OBJECT_ID ('vwRandom', 'V') IS NOT NULL
DROP VIEW vwRandom ;
GO

-- Used to reference RAND within a function
CREATE VIEW vwRandom
AS
SELECT RAND() as RandomValue;
GO

-- Drop the view if it already exists
IF OBJECT_ID ('vwRandom', 'V') IS NOT NULL
DROP VIEW vwRandom ;
GO
-- Used to reference RAND within a function
CREATE VIEW vwRandom
AS
SELECT RAND() as RandomValue;
GO

The Function

copy raw

-- Drop the function if it already exists
IF OBJECT_ID ('ufn_DataScramble', 'FN') IS NOT NULL
DROP FUNCTION ufn_DataScramble ;
GO

-- Create user defined function
CREATE FUNCTION ufn_DataScramble
(
    @OrigVal varchar(max)
)
RETURNS varchar(max)
WITH ENCRYPTION
AS
BEGIN

        -- Variables used
        DECLARE @NewVal varchar(max);
        DECLARE @OrigLen int;
        DECLARE @CurrLen int;
        DECLARE @LoopCt int;
        DECLARE @Rand int;
        DECLARE @RandString varchar(max);

        -- for dates/times
        DECLARE @MinYear int;
        DECLARE @MaxYear int;
        DECLARE @RandYear int;
        DECLARE @RandMonth int;
        DECLARE @RandDay int;
        DECLARE @RandHour int;
        DECLARE @RandMinute int;
        DECLARE @RandSeconds int;
        DECLARE @RandMSeconds int;
        DECLARE @RandChar varchar(1);
        DECLARE @RandNum int;
        DECLARE @DateVal date;
        DECLARE @TimeVal time;
        DECLARE @DateMatch int;
        DECLARE @TimeMatch int;
        DECLARE @ColumnTypeIsDate int;
        DECLARE @IntOptions int;
        DECLARE @CharOptions varchar(max);

        -- Format given value (prevents CHAR datatype issue)
        SET @OrigVal = LTRIM(RTRIM(@OrigVal));

        -- Set variable default values
        SET @NewVal = '';
        SET @OrigLen = LEN(CAST(@OrigVal AS VARCHAR(max)));
        SET @CurrLen = @OrigLen;
        SET @LoopCt = 1;
        SET @DateVal = ''; -- 1900-01-01
        SET @TimeVal = ''; -- 00:00:00.000
        SET @DateMatch = 0;
        SET @TimeMatch = 0;
        SET @ColumnTypeIsDate = 0;

        -- Determine if Given Value is date
        IF @OrigVal IS NULL
                RETURN NULL;

        -- Determine if Given Value is date
        IF ISDATE(@OrigVal)=1
                SET @ColumnTypeIsDate = 1;

        -- Determine if Given Value is date (ISDATE does not recognize CAST AS DATE)
        IF @OrigLen=10
                AND (LEN(@OrigVal) - LEN(REPLACE(@OrigVal, '-', '')))=2
                AND ISNUMERIC(SUBSTRING(@OrigVal, 1, 4))=1
                AND ISNUMERIC(SUBSTRING(@OrigVal, 6, 2))=1
                AND ISNUMERIC(SUBSTRING(@OrigVal, 9, 2))=1
                SET @ColumnTypeIsDate = 1;


        -- Determine if this is a date/time value
        IF @ColumnTypeIsDate=1
        BEGIN

                -- DETERMINE IF THIS FIELD CONTAINS A DATE VALUE
                SET @DateMatch =
                        CASE
                                WHEN CAST(CAST(@OrigVal AS date) AS VARCHAR(10))'1900-01-01' THEN 1
                                ELSE 0
                        END;


                -- DETERMINE IF THIS FIELD CONTAINS A TIME VALUE
                SET @TimeMatch =
                        CASE
                                WHEN CAST(CAST(@OrigVal AS time) AS VARCHAR(10))'00:00:00.000' THEN 1
                                ELSE 0
                        END;


                -- DETERMINE TIME VALUE IF TIME WAS IN GIVEN VALUE
                IF @DateMatch=1
                BEGIN
                        -- SET YEAR RANGE (1912 - 1994: for Birthdate specifically: Older than 18 but younger than 100)
                        SET @MinYear = DATEPART(YEAR,GETDATE())-100;    -- 100 years ago from today
                        SET @MaxYear = DATEPART(YEAR,GETDATE())-18;             -- 18 years ago from today


                        -- GET RANDOM DATE VALUES
                        SELECT @RandYear = ((@MinYear + 1) - @MaxYear) * RandomValue + @MaxYear FROM dbo.vwRandom;
                        SELECT @RandMonth = (1 - 12) * RandomValue + 12 FROM dbo.vwRandom;
                        SELECT @RandDay = (1 - 28) * RandomValue + 28 FROM dbo.vwRandom;  -- prevent invalid dates (specifically 31 days in feb)
                        SET     @DateVal =
                                CAST(@RandYear AS CHAR(4)) + '-'
                                + STUFF(@RandMonth, 1, 0, REPLICATE('0', 2 - LEN(@RandMonth))) + '-'
                                + STUFF(@RandDay, 1, 0, REPLICATE('0', 2 - LEN(@RandDay)));
                END


                -- DETERMINE TIME VALUE IF TIME WAS IN GIVEN VALUE
                IF @TimeMatch=1
                BEGIN

                        -- SET RANDOM TIME VALUE (include Milliseconds)
                        SELECT @RandHour = (0 - 23) * RandomValue + 23 FROM dbo.vwRandom;
                        SELECT @RandMinute = (0 - 59) * RandomValue + 59 FROM dbo.vwRandom;
                        SELECT @RandSeconds = (0 - 59) * RandomValue + 59 FROM dbo.vwRandom;
                        SELECT @RandMSeconds = (0 - 999) * RandomValue + 999 FROM dbo.vwRandom;
                        SET @TimeVal =
                                STUFF(@RandHour, 1, 0, REPLICATE('0', 2 - LEN(@RandHour)))
                                + ':' + STUFF(@RandMinute, 1, 0, REPLICATE('0', 2 - LEN(@RandMinute)))
                                + ':' + STUFF(@RandSeconds, 1, 0, REPLICATE('0', 2 - LEN(@RandSeconds)))
                                + '.' + STUFF(@RandMSeconds, 1, 0, REPLICATE('0', 3 - LEN(@RandMSeconds)));
                        SET @TimeVal = CONVERT(time, @TimeVal, 14);

                END


                -- DETERMINE WHICH DATE PARTS WERE SUBMITTED
                IF @TimeMatch=1 AND @DateMatch=0
                        SET @NewVal = @TimeVal;
                ELSE IF @TimeMatch=0 AND @DateMatch=1
                        SET @NewVal = @DateVal;
                ELSE IF @TimeMatch=1 AND @DateMatch=1
                        SET @NewVal = CAST(@DateVal AS varchar(10)) + ' ' + CAST(@TimeVal AS varchar(16));
                ELSE
                        SET @NewVal = '';


                -- REDUCE THIS STRING TO THE SAME LENGTH AS SUBMITTED VALUE // not necessary for update
                -- SET @NewVal = SUBSTRING(LTRIM(RTRIM(CAST(@NewVal AS VARCHAR(30)))), 1, LEN(@OrigVal));

        END
        ELSE
        BEGIN

                    IF ISNUMERIC(@OrigVal)=1
                    BEGIN

                        SET @NewVal = '';

                                -- Loop through the characters passed
                                WHILE @LoopCt

-- Drop the function if it already exists
IF OBJECT_ID ('ufn_DataScramble', 'FN') IS NOT NULL
DROP FUNCTION ufn_DataScramble ;
GO
-- Create user defined function
CREATE FUNCTION ufn_DataScramble
(
@OrigVal varchar(max)
)
RETURNS varchar(max)
WITH ENCRYPTION
AS
BEGIN
-- Variables used
DECLARE @NewVal varchar(max);
DECLARE @OrigLen int;
DECLARE @CurrLen int;
DECLARE @LoopCt int;
DECLARE @Rand int;
DECLARE @RandString varchar(max);
-- for dates/times
DECLARE @MinYear int;
DECLARE @MaxYear int;
DECLARE @RandYear int;
DECLARE @RandMonth int;
DECLARE @RandDay int;
DECLARE @RandHour int;
DECLARE @RandMinute int;
DECLARE @RandSeconds int;
DECLARE @RandMSeconds int;
DECLARE @RandChar varchar(1);
DECLARE @RandNum int;
DECLARE @DateVal date;
DECLARE @TimeVal time;
DECLARE @DateMatch int;
DECLARE @TimeMatch int;
DECLARE @ColumnTypeIsDate int;
DECLARE @IntOptions int;
DECLARE @CharOptions varchar(max);
-- Format given value (prevents CHAR datatype issue)
SET @OrigVal = LTRIM(RTRIM(@OrigVal));
-- Set variable default values
SET @NewVal = '';
SET @OrigLen = LEN(CAST(@OrigVal AS VARCHAR(max)));
SET @CurrLen = @OrigLen;
SET @LoopCt = 1;
SET @DateVal = ''; -- 1900-01-01
SET @TimeVal = ''; -- 00:00:00.000
SET @DateMatch = 0;
SET @TimeMatch = 0;
SET @ColumnTypeIsDate = 0;
-- Determine if Given Value is date
IF @OrigVal IS NULL
RETURN null;
-- Determine if Given Value is date
IF ISDATE(@OrigVal)=1
SET @ColumnTypeIsDate = 1;
-- Determine if Given Value is date (ISDATE does not recognize CAST AS DATE)
IF @OrigLen=10
AND (LEN(@OrigVal) - LEN(REPLACE(@OrigVal, '-', '')))=2
AND ISNUMERIC(SUBSTRING(@OrigVal, 1, 4))=1
AND ISNUMERIC(SUBSTRING(@OrigVal, 6, 2))=1
AND ISNUMERIC(SUBSTRING(@OrigVal, 9, 2))=1
SET @ColumnTypeIsDate = 1;
-- Determine if this is a date/time value
IF @ColumnTypeIsDate=1
BEGIN
-- DETERMINE IF THIS FIELD CONTAINS A DATE VALUE
SET @DateMatch =
CASE
WHEN CAST(CAST(@OrigVal AS date) AS VARCHAR(10))'1900-01-01' THEN 1
ELSE 0
END;
-- DETERMINE IF THIS FIELD CONTAINS A TIME VALUE
SET @TimeMatch =
CASE
WHEN CAST(CAST(@OrigVal AS time) AS VARCHAR(10))'00:00:00.000' THEN 1
ELSE 0
END;
-- DETERMINE TIME VALUE IF TIME WAS IN GIVEN VALUE
IF @DateMatch=1
BEGIN
-- SET YEAR RANGE (1912 - 1994: for Birthdate specifically: Older than 18 but younger than 100)
SET @MinYear = DATEPART(YEAR,GETDATE())-100; -- 100 years ago from today
SET @MaxYear = DATEPART(YEAR,GETDATE())-18; -- 18 years ago from today
-- GET RANDOM DATE VALUES
SELECT @RandYear = ((@MinYear + 1) - @MaxYear) * RandomValue + @MaxYear FROM dbo.vwRandom;
SELECT @RandMonth = (1 - 12) * RandomValue + 12 FROM dbo.vwRandom;
SELECT @RandDay = (1 - 28) * RandomValue + 28 FROM dbo.vwRandom; -- prevent invalid dates (specifically 31 days in feb)
SET @DateVal =
CAST(@RandYear AS CHAR(4)) + '-'
+ STUFF(@RandMonth, 1, 0, REPLICATE('0', 2 - LEN(@RandMonth))) + '-'
+ STUFF(@RandDay, 1, 0, REPLICATE('0', 2 - LEN(@RandDay)));
END
-- DETERMINE TIME VALUE IF TIME WAS IN GIVEN VALUE
IF @TimeMatch=1
BEGIN
-- SET RANDOM TIME VALUE (include Milliseconds)
SELECT @RandHour = (0 - 23) * RandomValue + 23 FROM dbo.vwRandom;
SELECT @RandMinute = (0 - 59) * RandomValue + 59 FROM dbo.vwRandom;
SELECT @RandSeconds = (0 - 59) * RandomValue + 59 FROM dbo.vwRandom;
SELECT @RandMSeconds = (0 - 999) * RandomValue + 999 FROM dbo.vwRandom;
SET @TimeVal =
STUFF(@RandHour, 1, 0, REPLICATE('0', 2 - LEN(@RandHour)))
+ ':' + STUFF(@RandMinute, 1, 0, REPLICATE('0', 2 - LEN(@RandMinute)))
+ ':' + STUFF(@RandSeconds, 1, 0, REPLICATE('0', 2 - LEN(@RandSeconds)))
+ '.' + STUFF(@RandMSeconds, 1, 0, REPLICATE('0', 3 - LEN(@RandMSeconds)));
SET @TimeVal = CONVERT(time, @TimeVal, 14);
END
-- DETERMINE WHICH DATE PARTS WERE SUBMITTED
IF @TimeMatch=1 AND @DateMatch=0
SET @NewVal = @TimeVal;
ELSE IF @TimeMatch=0 AND @DateMatch=1
SET @NewVal = @DateVal;
ELSE IF @TimeMatch=1 AND @DateMatch=1
SET @NewVal = CAST(@DateVal AS varchar(10)) + ' ' + CAST(@TimeVal AS varchar(16));
ELSE
SET @NewVal = '';
-- REDUCE THIS STRING TO THE SAME LENGTH AS SUBMITTED VALUE // not necessary for update
-- SET @NewVal = SUBSTRING(LTRIM(RTRIM(CAST(@NewVal AS VARCHAR(30)))), 1, LEN(@OrigVal));
END
ELSE
BEGIN
IF ISNUMERIC(@OrigVal)=1
BEGIN
SET @NewVal = '';
-- Loop through the characters passed
WHILE @LoopCt

Category: Transact-SQL :: Article: 460

DataScramble - Randomizing data rows

Credit where Credit is Due:

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Donate & Support