DataScramble - Randomizing data rows

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:
copyraw
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
  1.  Before: 
  2.             StudentID   StudentName          DateOfBirth 
  3.             ----------- -------------------- ------------- 
  4.             1           John Smith           1990-03-21 
  5.             2           Fred Bloggs          1988-11-02 
  6.             3           Another User         1985-07-11 
  7.             4           Yet Another User     1977-06-25 
  8.   
  9.         After: 
  10.             StudentID   StudentName          DateOfBirth 
  11.             ----------- -------------------- ------------- 
  12.             1           PDUHjRWJcb           1926-01-02 
  13.             2           WRmNqQKxvuV          1969-03-14 
  14.             3           nBCkAVDrvdhe         1968-05-05 
  15.             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.
copyraw
-- 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
  1.  -- Drop the view if it already exists 
  2.  IF OBJECT_ID ('vwRandom', 'V') IS NOT NULL 
  3.  DROP VIEW vwRandom ; 
  4.  GO 
  5.   
  6.  -- Used to reference RAND within a function 
  7.  CREATE VIEW vwRandom 
  8.  AS 
  9.  SELECT RAND() as RandomValue; 
  10.  GO 

The Function
copyraw
-- 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
  1.  -- Drop the function if it already exists 
  2.  IF OBJECT_ID ('ufn_DataScramble', 'FN') IS NOT NULL 
  3.  DROP FUNCTION ufn_DataScramble ; 
  4.  GO 
  5.   
  6.  -- Create user defined function 
  7.  CREATE FUNCTION ufn_DataScramble 
  8.  ( 
  9.      @OrigVal varchar(max) 
  10.  ) 
  11.  RETURNS varchar(max) 
  12.  WITH ENCRYPTION 
  13.  AS 
  14.  BEGIN 
  15.   
  16.          -- Variables used 
  17.          DECLARE @NewVal varchar(max)
  18.          DECLARE @OrigLen int; 
  19.          DECLARE @CurrLen int; 
  20.          DECLARE @LoopCt int; 
  21.          DECLARE @Rand int; 
  22.          DECLARE @RandString varchar(max)
  23.   
  24.          -- for dates/times 
  25.          DECLARE @MinYear int; 
  26.          DECLARE @MaxYear int; 
  27.          DECLARE @RandYear int; 
  28.          DECLARE @RandMonth int; 
  29.          DECLARE @RandDay int; 
  30.          DECLARE @RandHour int; 
  31.          DECLARE @RandMinute int; 
  32.          DECLARE @RandSeconds int; 
  33.          DECLARE @RandMSeconds int; 
  34.          DECLARE @RandChar varchar(1)
  35.          DECLARE @RandNum int; 
  36.          DECLARE @DateVal date; 
  37.          DECLARE @TimeVal time; 
  38.          DECLARE @DateMatch int; 
  39.          DECLARE @TimeMatch int; 
  40.          DECLARE @ColumnTypeIsDate int; 
  41.          DECLARE @IntOptions int; 
  42.          DECLARE @CharOptions varchar(max)
  43.   
  44.          -- Format given value (prevents CHAR datatype issue) 
  45.          SET @OrigVal = LTRIM(RTRIM(@OrigVal))
  46.   
  47.          -- Set variable default values 
  48.          SET @NewVal = ''
  49.          SET @OrigLen = LEN(CAST(@OrigVal AS VARCHAR(max)))
  50.          SET @CurrLen = @OrigLen; 
  51.          SET @LoopCt = 1
  52.          SET @DateVal = ''-- 1900-01-01 
  53.          SET @TimeVal = ''-- 00:00:00.000 
  54.          SET @DateMatch = 0
  55.          SET @TimeMatch = 0
  56.          SET @ColumnTypeIsDate = 0
  57.   
  58.          -- Determine if Given Value is date 
  59.          IF @OrigVal IS NULL 
  60.                  RETURN null
  61.   
  62.          -- Determine if Given Value is date 
  63.          IF ISDATE(@OrigVal)=1 
  64.                  SET @ColumnTypeIsDate = 1
  65.   
  66.          -- Determine if Given Value is date (ISDATE does not recognize CAST AS DATE) 
  67.          IF @OrigLen=10 
  68.                  AND (LEN(@OrigVal) - LEN(REPLACE(@OrigVal, '-', '')))=2 
  69.                  AND ISNUMERIC(SUBSTRING(@OrigVal, 1, 4))=1 
  70.                  AND ISNUMERIC(SUBSTRING(@OrigVal, 6, 2))=1 
  71.                  AND ISNUMERIC(SUBSTRING(@OrigVal, 9, 2))=1 
  72.                  SET @ColumnTypeIsDate = 1
  73.   
  74.   
  75.          -- Determine if this is a date/time value 
  76.          IF @ColumnTypeIsDate=1 
  77.          BEGIN 
  78.   
  79.                  -- DETERMINE IF THIS FIELD CONTAINS A DATE VALUE 
  80.                  SET @DateMatch = 
  81.                          CASE 
  82.                                  WHEN CAST(CAST(@OrigVal AS date) AS VARCHAR(10))'1900-01-01' THEN 1 
  83.                                  ELSE 0 
  84.                          END; 
  85.   
  86.   
  87.                  -- DETERMINE IF THIS FIELD CONTAINS A TIME VALUE 
  88.                  SET @TimeMatch = 
  89.                          CASE 
  90.                                  WHEN CAST(CAST(@OrigVal AS time) AS VARCHAR(10))'00:00:00.000' THEN 1 
  91.                                  ELSE 0 
  92.                          END; 
  93.   
  94.   
  95.                  -- DETERMINE TIME VALUE IF TIME WAS IN GIVEN VALUE 
  96.                  IF @DateMatch=1 
  97.                  BEGIN 
  98.                          -- SET YEAR RANGE (1912 - 1994: for Birthdate specifically: Older than 18 but younger than 100) 
  99.                          SET @MinYear = DATEPART(YEAR,GETDATE())-100;    -- 100 years ago from today 
  100.                          SET @MaxYear = DATEPART(YEAR,GETDATE())-18;             -- 18 years ago from today 
  101.   
  102.   
  103.                          -- GET RANDOM DATE VALUES 
  104.                          SELECT @RandYear = ((@MinYear + 1) - @MaxYear) * RandomValue + @MaxYear FROM dbo.vwRandom; 
  105.                          SELECT @RandMonth = (1 - 12) * RandomValue + 12 FROM dbo.vwRandom; 
  106.                          SELECT @RandDay = (1 - 28) * RandomValue + 28 FROM dbo.vwRandom;  -- prevent invalid dates (specifically 31 days in feb) 
  107.                          SET     @DateVal = 
  108.                                  CAST(@RandYear AS CHAR(4)) + '-' 
  109.                                  + STUFF(@RandMonth, 1, 0, REPLICATE('0', 2 - LEN(@RandMonth))) + '-' 
  110.                                  + STUFF(@RandDay, 1, 0, REPLICATE('0', 2 - LEN(@RandDay)))
  111.                  END 
  112.   
  113.   
  114.                  -- DETERMINE TIME VALUE IF TIME WAS IN GIVEN VALUE 
  115.                  IF @TimeMatch=1 
  116.                  BEGIN 
  117.   
  118.                          -- SET RANDOM TIME VALUE (include Milliseconds) 
  119.                          SELECT @RandHour = (0 - 23) * RandomValue + 23 FROM dbo.vwRandom; 
  120.                          SELECT @RandMinute = (0 - 59) * RandomValue + 59 FROM dbo.vwRandom; 
  121.                          SELECT @RandSeconds = (0 - 59) * RandomValue + 59 FROM dbo.vwRandom; 
  122.                          SELECT @RandMSeconds = (0 - 999) * RandomValue + 999 FROM dbo.vwRandom; 
  123.                          SET @TimeVal = 
  124.                                  STUFF(@RandHour, 1, 0, REPLICATE('0', 2 - LEN(@RandHour))) 
  125.                                  + ':' + STUFF(@RandMinute, 1, 0, REPLICATE('0', 2 - LEN(@RandMinute))) 
  126.                                  + ':' + STUFF(@RandSeconds, 1, 0, REPLICATE('0', 2 - LEN(@RandSeconds))) 
  127.                                  + '.' + STUFF(@RandMSeconds, 1, 0, REPLICATE('0', 3 - LEN(@RandMSeconds)))
  128.                          SET @TimeVal = CONVERT(time, @TimeVal, 14)
  129.   
  130.                  END 
  131.   
  132.   
  133.                  -- DETERMINE WHICH DATE PARTS WERE SUBMITTED 
  134.                  IF @TimeMatch=1 AND @DateMatch=0 
  135.                          SET @NewVal = @TimeVal; 
  136.                  ELSE IF @TimeMatch=0 AND @DateMatch=1 
  137.                          SET @NewVal = @DateVal; 
  138.                  ELSE IF @TimeMatch=1 AND @DateMatch=1 
  139.                          SET @NewVal = CAST(@DateVal AS varchar(10)) + ' ' + CAST(@TimeVal AS varchar(16))
  140.                  ELSE 
  141.                          SET @NewVal = ''
  142.   
  143.   
  144.                  -- REDUCE THIS STRING TO THE SAME LENGTH AS SUBMITTED VALUE // not necessary for update 
  145.                  -- SET @NewVal = SUBSTRING(LTRIM(RTRIM(CAST(@NewVal AS VARCHAR(30)))), 1, LEN(@OrigVal))
  146.   
  147.          END 
  148.          ELSE 
  149.          BEGIN 
  150.   
  151.                      IF ISNUMERIC(@OrigVal)=1 
  152.                      BEGIN 
  153.   
  154.                          SET @NewVal = ''
  155.   
  156.                                  -- Loop through the characters passed 
  157.                                  WHILE @LoopCt 
Category: Transact-SQL :: Article: 460

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.