I would like to improve the following function to randomize the letters in an email column. So far I have the following function but the output is not what I expected:
CREATE VIEW dbo.vwRandom
AS
SELECT RAND() as RandomValue;
GO
CREATE FUNCTION dbo.Character_Scramble
(
@OrigVal varchar(MAX)
)
RETURNS varchar(MAX)
WITH ENCRYPTION
AS
BEGIN
-- Variables used
DECLARE @NewVal VARCHAR(MAX);
DECLARE @OrigLen INT;
DECLARE @CurrLen INT;
DECLARE @LoopCt INT;
DECLARE @Rand INT;
-- Set variable default values
SET @NewVal = '';
SET @OrigLen = DATALENGTH(@OrigVal);
SET @CurrLen = @OrigLen;
SET @LoopCt = 1;
-- Loop through the characters passed
WHILE @LoopCt <= @OrigLen
BEGIN
-- Current length of possible characters
SET @CurrLen = DATALENGTH(CHARINDEX('@', @OrigVal));
-- Random position of character to use
SELECT
@Rand = Convert(int,(((1) - @CurrLen) *
RandomValue + @CurrLen))
FROM
dbo.vwRandom;
-- Assembles the value to be returned
SET @NewVal =
SUBSTRING(@OrigVal,@Rand,1) + @NewVal;
-- Removes the character from available options
SET @OrigVal =
Replace(@OrigVal,SUBSTRING(@Origval,@Rand,1),'');
-- Advance the loop="color:black">
SET @LoopCt = @LoopCt + 1;
END
-- Returns new value
Return LOWER(@NewVal);
END
GO
The output returned by the function is:
SELECT dbo.Character_Scramble('waltero.lukase@gmail.com')
-- output: vmgoli.@cuares
The output I want would be to respect the length of the word and the position of the symbols (., @, _, etc.).
SELECT dbo.Character_Scramble('waltero.lukase@gmail.com')
-- pkderso.modefk@poajf.lpd
Any help would help me enormously. Thank you!
Aucun commentaire:
Enregistrer un commentaire