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:
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.
-- 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
-- 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 <= @OrigLen
BEGIN
-- GET RANDOM NUMBER VALUES
SELECT @RandString = (0 - 9) * RandomValue + 9 FROM dbo.vwRandom;
-- Add to value to return
SET @NewVal = @NewVal + CAST(@RandString AS VARCHAR(1));
-- Advance the loop
SET @LoopCt = @LoopCt + 1;
END
END
ELSE
BEGIN
-- Loop through the characters passed
WHILE @LoopCt <= @OrigLen
BEGIN
SET @RandString = 'ABCDEFGHJKLMNPQURSUVWXYZabcdefghjkmnpqursuvwxyz';
-- GET RANDOM NUMBER VALUES
SELECT @RandNum = (1 - LEN(@RandString)) * RandomValue + LEN(@RandString) FROM dbo.vwRandom;
-- Add to value to return
SET @NewVal = @NewVal + SUBSTRING(@RandString,@RandNum,1);
-- Advance the loop
SET @LoopCt = @LoopCt + 1;
END
END
-- Format new value
SET @NewVal = LTRIM(RTRIM(@NewVal));
END
-- Return new value
RETURN @NewVal;
END
GO
Discussion
Comments
Questions, corrections and useful additions are reviewed before appearing here.
No comments yet. Start the discussion.