Data scrubbing using SQL functions for SQL Server

Many a times, it is required to scrub the data before it is used in development environments as the data may be sensitive and cannot be shared with the vendors. The  below SQL functions can be used to scrub the data.


--Creating the User defined function 
  CREATE FUNCTION getRandomText(@sLength TINYINT)
  RETURNS VARCHAR(10)
  AS
                BEGIN
                 DECLARE @counter TINYINT
                 DECLARE @nextChar CHAR(1)
                 DECLARE @randomString VARCHAR(50)
 
                 SET @sLength=10
                 SET @counter = 1
                 SET @randomString = ''

                 WHILE @counter <= @sLength
                 BEGIN
                 SELECT @nextChar = CHAR(ROUND((SELECT r FROM getRand) * 93 + 33, 0))
                 IF ( (ASCII(@nextChar) between 65 and 90)  or (ASCII(@nextChar) between 97 and 122) )
                   BEGIN
                   SELECT @randomString = @randomString + @nextChar
                   SET @counter = @counter + 1
                   END
                 END
                return @randomString
                END
               
    GO
   
   -- Creating the User defined function
  CREATE FUNCTION getRandomNumber()
                RETURNS INT
                AS
                                BEGIN
                                                RETURN ROUND((SELECT r FROM getRand) * 10000, 0)
                                END
                GO

Comments

Post a Comment

Popular posts from this blog

The famous Vanilla story --

This is a weird but true  story ...  A complaint was received by the Pontiac Division of General Motors:  This is the second time I have written you, and I don't blame you for not answering me, because I kind of sounded crazy, but it is a fact that we have a tradition in our family of ice cream for dessert after dinner each night. But the kind of ice cream varies so, every night, after we've eaten, the whole family votes on which kind of ice cream we should have and I drive down to the store to get it.  It's also a fact that I recently purchased a new Pontiac and since then my trips to the store have created a problem. You see, every time I buy  vanilla ice cream, when I start back from the store my car won't start. If I get any other kind of ice cream, the car starts just fine.  I want you to know I'm serious about this question, no matter how silly it sounds: 'What is there about a Pontiac that makes it not start when I get vanilla ice crea...
Read more

Convert fetchXML to SQL Query in MSCRM 4.0

If for some sane reasons if fetchxml needs to be converted to a SQL Query in MSCRM 4.0, it is possible. It actually requires the below DLL's which are avilable in the folder Server\setup Microsoft.Crm.dll, Microsoft.Crm.Platform.Server.dll and Microsoft.Crm.Reporting.dll The code looks as below : Microsoft.Crm.BusinessEntities.ExecutionContext myContext = new Microsoft.Crm.BusinessEntities. ExecutionContext (OrganizationId from the context); myContext.OnBeginRequest(UserId, false); Microsoft.Crm.Reporting.ReportQueryBuilder myreportQueryBuilderObj = new ReportQueryBuilder(); string Query = myreportQueryBuilderObj.BuildReportQuery(fetchXml, myContext); The query will be returned as string. The Query is not readable but it works, -:)
Read more

DOGL - Day ONE GO LIVE

DAY 1 GO LIVE ------------------------- Challenges at Hand: • Many IT projects fail due to challenges uncovered in the later phases of the project like SIT, UAT and deploy. There by, the organizations lose billions of dollars. • The business process change more rapidly than ever, making the IT projects more challenging. When the project comes into UAT phase, at least 20% of the requirements are changed. • The existing methodologies like waterfall, agile have failed to minimize the failure of projects. It clearly indicates that there is a need for a new methodology. Solution: DOGL, day one GO LIVE is a new methodology which aims for a technical go live on day1 of the project. This aims to minimize the failure of projects or minimize the losses by identifying the challenges/issues early in the game. As early as day 1. DOGL methodology aims to build process to address the  challenges faced in waterfall methodology and others. DOGL aims to yield the business earned value from day...
Read more