Scrambling VARCHAR / Text Data in SQL Server

Everyone wants to scramble their production data before they give it out for testing.

I also had the same requirement.

However, I was required to do something more.

Scrambled Data should pass some regular expression validations.

For example, Name should still be First_Name Last_Name, Email Address should look like a valid email address, etc.

Like Jack Sparrow to become Dchv Dkiokmd. 

And, Jack.Sparrow@pirate27.biz to become Ninj.Sdkpiqy@wywmky53.kdd.

Here is what I have done.


-------------------------------------------------

--A view to give you Random Values
CREATE VIEW dbo.random(value) AS SELECT RAND();

GO
-------------------------------------------------

--Randomization Procedure
CREATE FUNCTION dbo.fnRandomizedText (
@OldValue AS VARCHAR(MAX)
)RETURNS VARCHAR(MAX)

BEGIN

  DECLARE @NewValue AS VARCHAR(MAX)
  DECLARE @nCount AS INT
  DECLARE @cCurrent AS CHAR(1)
  DECLARE @cScrambled AS CHAR(1)
  DECLARE @Random AS REAL
 
  SET @NewValue = ''
  SET @nCount = 0
   WHILE (@nCount <= LEN(@OldValue))
  BEGIN
    SELECT @Random = value FROM random
    SET @cCurrent = SUBSTRING(@OldValue, @nCount, 1)
     IF ASCII(@cCurrent) BETWEEN ASCII('a') AND ASCII('z')
       SET @cScrambled = CHAR(ROUND(((ASCII('z') - ASCII('a') - 1) * @Random + ASCII('a')), 0))
    ELSE IF ASCII(@cCurrent) BETWEEN ASCII('A') AND ASCII('Z')
       SET @cScrambled = CHAR(ROUND(((ASCII('Z') - ASCII('A') - 1) * @Random + ASCII('A')), 0))
    ELSE IF ASCII(@cCurrent) BETWEEN ASCII('0') AND ASCII('9')
       SET @cScrambled = CHAR(ROUND(((ASCII('9') - ASCII('0') - 1) * @Random + ASCII('0')), 0))
    ELSE
       SET @cScrambled = @cCurrent

    SET @NewValue = @NewValue + @cScrambled
    SET @nCount = @nCount + 1

  END
   RETURN LTRIM(RTRIM(@NewValue))
END
GO 
-------------------------------------------------

With this Function, you can update your tables just like this.

UPDATE my_Users_table SET UserName = dbo.fnRandomizedText(UserName), LoweredUserName = dbo.fnRandomizedText(LoweredUserName)