Scramble/Obfuscate email values in SQL Server

Question

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('[email protected]')
-- 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('[email protected]')
-- [email protected]

Any help would help me enormously. Thank you!

Answer 1

Maybe not exactly what you are doing, but the solution randomize the sequence of the alphabet set a-z and then use translate() to translate from normal sequence (a to z) to the randomize sequence

Due to string_agg() constraint, it cannot have two order by in the same scope, 2 separate CTE is used to generate the @normal and @scramble

declare @original varchar(100) = '[email protected]';

declare @normal    varchar(26),
        @scramble  varchar(26);

with chars as
(
    select *
    from
    (
        values ('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')
    ) a (a)
),
normal as
(    
    -- normal ordering : a to z 
    select normal   = string_agg(a, '') within group (order by a)
    from   chars
),
scramble as
(
      -- randomize ordering
      select scramble = string_agg(a, '') within group (order by newid())
      from  chars
)
select @normal   = normal,
       @scramble = scramble
from   normal cross join scramble

select original  = @original, 
       scrambled = translate(@original, @normal, @scramble);

UPDATE: to apply this to your table, just add the FROM to the end of the query and change @original to your table name

with chars as
.  .  .
select  original  = email, 
        scrambled = translate(email, @normal, @scramble)
from    yourtable

db<>fiddle demo