Thursday, December 20, 2012

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)

4 comments:

Rebecca@sqlfingers said...

Excellent! Thank you for the post.

Unknown said...
This comment has been removed by the author.
Mrs. McCarthy Juan said...

TODAY I GOT MY DESIRED XMAS LOAN AMOUNT $520,000.00 FROM A RELIABLE AND TRUSTED LOAN COMPANY. IF YOU NEED A LOAN NOW EMAIL CONTACT drbenjaminfinance@gmail.com

Hello, I'm here to testify of how i got my loan from BENJAMIN LOAN FINANCE(drbenjaminfinance@gmail.com) I don't know if you are in need of an urgent loan to pay bills, start business or build a house, they offer all kinds of loan. So feel free to contact Dr. Benjamin Owen he holds all of the information about how to obtain money quickly and painlessly without cost/stress via Email: drbenjaminfinance@gmail.com

Consider all your financial problems tackled and solved ASAP. Share this to help a soul right now THANKS.

Francesco said...

Thank you Techno Brat