I have looked over nearly all the possible articles to try and solve the below but they all contain a mix of letters & numbers, usually using NEWID().
Background: I am looking to Obfuscate/scramble data in a db (sensitive data only - name, phone address, account number etc.) Due to laws surrounding the transfer of this data from 1 country to another it is vital that it be completely random and in no way just a mix up of the original data - i.e. FullName = Chris Carroll becomes Fullname = HSLO LCRAIRCR. for anyone who is okay with that level see a good article here - (http://ift.tt/1m7aXPz)
Essentially I need the data to stay in the same format but randomise letters for letters and numbers for numbers, all spaces and special characters should remain the same at all times for testing invalid inputs.
so essentially if FullName = John-Paul Jones it should now be equal to something like 'KDQV-TGIW BMZRD' 4 letters, special character, 4 letters, space, 5 letters.
If anyone knows a way for me to do this I would really appreciate guidance as I have hit a brick wall myself. thanks
the closest I have gotten has been to use the REPLACE function for all 26 letters, but once I got over about 10 nested REPLACEs it doesn't produce the correct data. It only uses about 4 or 5 letters in the randomising which in theory works but I need to be able to show that each letter correlates with what it should.
I have declared and set variables for each letter of the alphabet, set it to a random letter, and then called on the variable by itself and then also within a select nested REPLACE function. if I have less than 10 nested REPLACEs both results match but anything over that and they won't. (also selecting original FullName in order to compare results
In an ideal world i would love to have the 'letter' obfuscation to work as a function that I could just call upon in my scripts, not sure how to do anything that advanced though, if anyone does know both how to resolve the below and also how to create a function with it, that would be perfect.
here is my SQL if anyone can help;
USE [DATABASE1]
DECLARE @A AS CHAR;
DECLARE @B AS CHAR;
DECLARE @C AS CHAR;
DECLARE @D AS CHAR;
DECLARE @E AS CHAR;
DECLARE @F AS CHAR;
DECLARE @G AS CHAR;
DECLARE @H AS CHAR;
DECLARE @I AS CHAR;
DECLARE @J AS CHAR;
DECLARE @K AS CHAR;
DECLARE @L AS CHAR;
DECLARE @M AS CHAR;
DECLARE @N AS CHAR;
DECLARE @O AS CHAR;
DECLARE @P AS CHAR;
DECLARE @Q AS CHAR;
DECLARE @R AS CHAR;
DECLARE @S AS CHAR;
DECLARE @T AS CHAR;
DECLARE @U AS CHAR;
DECLARE @V AS CHAR;
DECLARE @W AS CHAR;
DECLARE @X AS CHAR;
DECLARE @Y AS CHAR;
DECLARE @Z AS CHAR;
SET @A = CHAR(CAST((90 - 65) * RAND() + 65 AS INT));
SET @B = CHAR(CAST((90 - 65) * RAND() + 65 AS INT));
SET @C = CHAR(CAST((90 - 65) * RAND() + 65 AS INT));
SET @D = CHAR(CAST((90 - 65) * RAND() + 65 AS INT));
SET @E = CHAR(CAST((90 - 65) * RAND() + 65 AS INT));
SET @F = CHAR(CAST((90 - 65) * RAND() + 65 AS INT));
SET @G = CHAR(CAST((90 - 65) * RAND() + 65 AS INT));
SET @H = CHAR(CAST((90 - 65) * RAND() + 65 AS INT));
SET @I = CHAR(CAST((90 - 65) * RAND() + 65 AS INT));
SET @J = CHAR(CAST((90 - 65) * RAND() + 65 AS INT));
SET @K = CHAR(CAST((90 - 65) * RAND() + 65 AS INT));
SET @L = CHAR(CAST((90 - 65) * RAND() + 65 AS INT));
SET @M = CHAR(CAST((90 - 65) * RAND() + 65 AS INT));
SET @N = CHAR(CAST((90 - 65) * RAND() + 65 AS INT));
SET @O = CHAR(CAST((90 - 65) * RAND() + 65 AS INT));
SET @P = CHAR(CAST((90 - 65) * RAND() + 65 AS INT));
SET @Q = CHAR(CAST((90 - 65) * RAND() + 65 AS INT));
SET @R = CHAR(CAST((90 - 65) * RAND() + 65 AS INT));
SET @S = CHAR(CAST((90 - 65) * RAND() + 65 AS INT));
SET @T = CHAR(CAST((90 - 65) * RAND() + 65 AS INT));
SET @U = CHAR(CAST((90 - 65) * RAND() + 65 AS INT));
SET @V = CHAR(CAST((90 - 65) * RAND() + 65 AS INT));
SET @W = CHAR(CAST((90 - 65) * RAND() + 65 AS INT));
SET @X = CHAR(CAST((90 - 65) * RAND() + 65 AS INT));
SET @Y = CHAR(CAST((90 - 65) * RAND() + 65 AS INT));
SET @Z = CHAR(CAST((90 - 65) * RAND() + 65 AS INT));
SELECT @A AS A
,@B AS B
,@C AS C
,@D AS D
,@E AS E
,@F AS F
,@G AS G
,@H AS H
,@I AS I
,@J AS J
,@K AS K
,@L AS L
,@M AS M
,@N AS N
,@O AS O
,@P AS P
,@Q AS Q
,@R AS R
,@S AS S
,@T AS T
,@U AS U
,@V AS V
,@W AS W
,@X AS X
,@Y AS Y
,@Z AS Z
SELECT c.ClientFullName
,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(c.ClientFullName, 'A', @A), 'B', @B), 'C', @C), 'D', @D), 'E', @E), 'F', @F), 'G', @G), 'H', @H), 'I', @I), 'J', @J), 'K', @K), 'L', @L), 'M', @M), 'N', @N), 'O', @O), 'P', @P), 'Q', @Q), 'R', @R), 'S', @S), 'T', @T), 'U', @U), 'V', @V), 'W', @W), 'X', @X), 'Y', @Y), 'Z', @Z)
FROM [DATABASE1].[dbo].[CLIENTS] c
thank you in advance to anyone who took the time to read this
Aucun commentaire:
Enregistrer un commentaire