Hi,

I need to scrambled data for a column which has 9 characters or more and numbers

conditions..

1) if the column are all numbers and length of the column is 9 then keep the 1st 3 characters same and the rest 6 numbers should be scrambled by converting each character to ASCII value  then use 5 arthematic opertors to get a new number  then by using substring to get the 6 character value then joining first 3 letters with  6 letter that we have scrambled.
for eg: let say we have 345678900 then first 3 numbers should be same '345' then the rest should be scrambled like this taking '678900'.. the ascii value for this number is 545556574848 then use 5 arthematic operator to get a new value and then we can use substring to get 6 charactor value. and the final out will be adding the first 3 with last 6.

2) if the column has character and numbers, for eg- a2345sd09 then first 3 character will be remained same and then with last 6 characters only the number should be scrambled. last 6 - '45sd09'. here sd will be remain same and all the numbers will be scrambled with different number.


and if it has extra character like /'(*&^%' for eg madman06/08 then here keeping the extra character and alphabets only the numbers will be scrambled.


can you guys help me how can i achieve this code?

Use the following code to create a scalar valued function...

DECLARE @SomeNumber varchar(20) = 'max!@#$78kmh';

WITH n (n) AS (
	SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)
), Tally (n) AS (
	SELECT TOP (LEN(@SomeNumber) -3)
		ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) +3
	FROM 
		n n1, n n2
), SplitNumber (RN, Number) AS (
	SELECT 
		ROW_NUMBER() OVER (ORDER BY NEWID()),
		SUBSTRING(@SomeNumber, t.n, 1)
	FROM Tally t
)
	SELECT LEFT(@SomeNumber, 3) + 
	(SELECT sn.Number
		FROM SplitNumber sn
		ORDER BY NEWID()
		FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)') AS ScrampledNumber

HTH,

Jason

edit: The above will cover the scrambling. Identifying and holding the position of special characters is going to be a bit more difficult. Doable but I don't have the time to code it right

Here is a version that will hold the special characters in their place. As you can see, it's a wee bit more complex than the 1st one.

DECLARE @SomeString varchar(20) = 'A2qbGt567&u8*';


WITH n (n) AS (
	SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)
), Tally (n) AS (
	SELECT TOP (LEN(@SomeString) -3)
		ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) +3
	FROM n n1, n n2
), RegularChars AS (
	SELECT an FROM (VALUES ('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('0'),
		('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j'),('k'),('l'),('m'),
		('n'),('o'),('p'),('q'),('r'),('s'),('t'),('u'),('v'),('w'),('x'),('y'),('z')
		) x (an)
), SplitNumber (RN, AlphaNum, an) AS (
		SELECT 
			ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
			SUBSTRING(@SomeString, t.n, 1),
			rc.an
		FROM 
			Tally t
			LEFT JOIN RegularChars rc
				ON SUBSTRING(@SomeString, t.n, 1) = rc.an
), RandomRegulars (x, RN) AS (
	SELECT 
		ROW_NUMBER() OVER (ORDER BY NEWID()),
		sn.RN
	FROM 
		SplitNumber sn
	WHERE 
		sn.an IS NOT NULL
), SwapRows AS (
	SELECT 
		rr1.RN AS RN1,
		rr2.RN AS RN2
	FROM 
		RandomRegulars rr1
		JOIN RandomRegulars rr2
			ON rr1.x = rr2.x
)
	SELECT LEFT(@SomeString, 3) + 
	(SELECT sn.AlphaNum
		FROM SplitNumber sn
		LEFT JOIN SwapRows sw
			ON sn.RN = sw.RN1
		ORDER BY IIF(sn.an IS NULL, sn.rn, sw.RN2)
		FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)') AS ScrampledNumber;