mardi 12 avril 2022

Scramble/Obfuscate email values in SQL Server

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