Monday, July 28, 2014
   
Text Size
Login

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:

  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.

  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

  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 <= @OrigLen 
  158.                                          BEGIN 
  159.   
  160.                                          -- GET RANDOM NUMBER VALUES 
  161.                                          SELECT @RandString = (0 - 9) * RandomValue + 9 FROM dbo.vwRandom; 
  162.   
  163.                                          -- Add to value to return 
  164.                                          SET @NewVal = @NewVal + CAST(@RandString AS VARCHAR(1)); 
  165.   
  166.                                          -- Advance the loop 
  167.                                          SET @LoopCt = @LoopCt + 1; 
  168.                                          END 
  169.   
  170.                     END 
  171.                     ELSE 
  172.                     BEGIN 
  173.   
  174.                          -- Loop through the characters passed 
  175.                          WHILE @LoopCt <= @OrigLen 
  176.                          BEGIN 
  177.   
  178.                                  SET @RandString = 'ABCDEFGHJKLMNPQURSUVWXYZabcdefghjkmnpqursuvwxyz'
  179.   
  180.                                  -- GET RANDOM NUMBER VALUES 
  181.                                  SELECT @RandNum = (1 - LEN(@RandString)) * RandomValue + LEN(@RandStringFROM dbo.vwRandom; 
  182.   
  183.                                  -- Add to value to return 
  184.                                  SET @NewVal = @NewVal + SUBSTRING(@RandString,@RandNum,1); 
  185.   
  186.                                  -- Advance the loop 
  187.                                  SET @LoopCt = @LoopCt + 1; 
  188.   
  189.                          END  
  190.   
  191.                                   
  192.                     END 
  193.   
  194.                     -- Format new value 
  195.                     SET @NewVal = LTRIM(RTRIM(@NewVal)); 
  196.          END 
  197.   
  198.          -- Return new value 
  199.          RETURN @NewVal
  200.  END 
  201.  GO 
Add Comment

Name:

Email:

Website:

Message:


Latest Posts

  • Joes Quicklist Weblinks (JQW)

    • Thu 17-Jul-14
      Hi, thanks a lot. But I have got a problem with download it. Error: "File could not be found Important!
      Kat  
    • Tue 15-Jul-14
      Hi Artur, I forgot to get back to you sooner. I was going to include a set of instructions to do this ...
      Webmaster  
    • Mon 14-Jul-14
      Hi Skarosg, Not sure if we took this to e-mail but I noticed your site is displaying the images now.
      Webmaster  
    • Fri 11-Jul-14
      Hi, How can I put user name and category of weblink in module, near the date? Thanks..
      Artur
  • Joes Word Cloud (JWC)

    • Tue 17-Jun-14
      I'm using JE 3.1.19 stable.
      alve89