none
Obfuscating Data with using ASCII in UDF

    Question

  • Hi,

    I want to use ASCII in UDF to Masking Data of LNAME column like :

    SOURCE Table

    --------------------

    LNAME

    --------------------

    AAGOTNES
    ABATE
    ABBATI
    ABBONIZIO
    ABBOTT
    ABBOTT
    ABBOTT

    --------------

    As my requirement is By USING UDF( Logic is ASCII Read with each letter in LNAME +10 and again return with new with new letter)

    plz provide UDF to Mask data  with ASCII+ (add)10(LNAME)=Mask_LNAME

    Thanks

    MADHU

    Tuesday, October 08, 2013 7:44 AM

All replies

  • Hi

    DO you want some thing similar to below code

    Declare @text varchar(max)= 'AAGOTNES',
    @Resulttext varchar(max)= '',
    @i int = 1
    while(@i < len(@text))
    BEGIN
    select @Resulttext = @Resulttext + CHAR(ASCII(SUBSTRING(@text,@i,1))+10)
    set @i = @i+1
    END
    select @Resulttext
    select ASCII('A'),ASCII('K')

    Thanks

    Saravana Kumar C

    Tuesday, October 08, 2013 8:03 AM
  • Thank you .

    It has worked in Function

    One Query:

    Is there any possibility to avoid Special characters?

    Now I applied +4 but still getting [ ,$,..,

    -MADHU

    Tuesday, October 08, 2013 9:12 AM
  • These kind of ciphers require that you work on defined input and output.. E.g. ROT13.
    Tuesday, October 08, 2013 9:27 AM
  • Thank You very much.

    Its Good & Use full acc to INPUT & OUTPUT.

    Temporarily

    I have done in UDF like below UDF for Other SSN=@text but ONE more Query :

    If

          CASE  
                   WHEN @SSN  =NULL OR @SSN = '' or @SSN = 'NONE' THEN null     
                   WHEN @SSN  ='999-99-9999'    THEN '999-99-9999'   

    Note:@SSN=@Text in below UDF

    Where Should apply above CASE logic in below UDF ?

    Please help on by adding/Modify the below UDF.

    create function [dbo].[ASCII_mask_digit]
    (@text varchar(255)) 
    returns varchar(255)
    as
    BEGIN                  
    Declare @Resulttext varchar(max)='',
            @Result varchar(250)='',
            @i int = 1         
    while(@i <= len(right(@text,4)))
    BEGIN
    select @Resulttext =  @Resulttext + CHAR(ASCII(SUBSTRING(REVERSE(@text),@i,1))+1)
    set @i = @i+1
    END
    select @Result=replace((substring(@text,1,7)+@Resulttext),':',4)
    return @Result
    END

    Thanks

    -MADHU


    Tuesday, October 08, 2013 10:22 AM
  • E.g. something like

    DECLARE @Alphabet VARCHAR(255) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE @Length INT = LEN(@Alphabet); -- Length must be even.
    DECLARE @InputString VARCHAR(255) = '123-45-6789';
    DECLARE @Position INT = 1;
    DECLARE @OutputString VARCHAR(255) = '';
    
    WHILE @Position <= LEN(@InputString)
    BEGIN    
      SET @OutputString = @OutputString + 
        ISNULL(
    		SUBSTRING(@Alphabet, (
    			CASE WHEN CHARINDEX(SUBSTRING(@InputString, @Position, 1), @Alphabet) = 0 THEN NULL
    				 ELSE CHARINDEX(SUBSTRING(@InputString, @Position, 1), @Alphabet)
    			END
    			+ @Length / 2) % @Length, 1)
    		, SUBSTRING(@InputString, @Position, 1));
      SET @Position += 1;
    END;
    
    SELECT @OutputString;

    btw, due to the use of SSN: The concrete cases I know, were you need to do something like this, explicitly specify that you need to anonymize this kind of data. Obfuscation or any kind of encryption is not sufficent, as it can be reverted.
    Tuesday, October 08, 2013 11:49 AM
  • Thanks you  very much spending valuable time,

    Your answer is just little bit difficult to me  So

    I have done CASE logic in Below mentioned UDF:

    CREATE function [dbo].[ASCII_mask_digit_case](@text varchar(255)) returns varchar(255)asBEGIN                 Declare @Resulttext varchar(max)='',        @Result varchar(250)='',        @i int = 1         while(@i <= len(right(@text,4)))BEGINselect @Resulttext =  @Resulttext + CHAR(ASCII(SUBSTRING(REVERSE(@text),@i,1))+1)set @i = @i+1ENDselect @Result=CASE                                 WHEN @text  ='NULL' OR  @text = null or @text = '' or @text = 'NONE' THEN null                                    WHEN @text  ='999-99-9999'    THEN '999-99-9999'                WHEN @text  ='000-00-0000'    THEN '000-00-0000'                 else replace((substring(@text,1,7)+@Resulttext),':',4)               endreturn @ResultEND


    Is it OK ,  ?? to simple !!!

    _MADHU

    Tuesday, October 08, 2013 12:17 PM