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:

       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

Add comment


Send