fake id for the existing id

Zaran 21 Reputation points
2022-03-24T00:15:44.36+00:00

Hi,

I have a table EMP with EMP_id field and wantes to create fake EMP_id filed for each EMP_id like below. Could you please help me how to generate fake EMP_Id column for each EMP_Id

Table EMP:

EMP_Id

1020
10245
10200
50300

and result should be :

EMP_Id, Fake EMP_Id


1020 awert12d
10245 adai34kd
10200 dfd23234
50300 ekwekfl23

Developer technologies | Transact-SQL
{count} votes

2 answers

Sort by: Most helpful
  1. Ronen Ariely 15,206 Reputation points
    2022-03-24T00:45:55.707+00:00

    Hi,

    Not clear to me what are the rules regarding the fake EMP_Id which you want to get, but the following points should help you solve your needs probably

    (1) If the fake EMP_Id is random value then you can use one of the solutions to build random string which mentioned in following posts

    using TSQL: https://social.technet.microsoft.com/wiki/contents/articles/21196.t-sql-random-string.aspx

    Using CLR: https://ariely.info/Blog/tabid/83/EntryId/134/SQL-Random-String-using-CLR.aspx

    If the fake value is not random then you need to explain what the rules to create the fake fake EMP_Id, and we will try to help you generate the values

    let's assume that you finished step one and you have fake EMP_Id values...

    (2) If you need to have the same fake EMP_Id for each row always, then you must store that information in the table. This means that you need to add a column for the fake EMP_Id -> generate a fake EMP_Id as explain in point (1) and store it

    (3) If you simply need a fake EMP_Id then you can use the string you generate as in point (1) or better solution is to use Dynamic Data Masking

    My first guess based on the information we have (which is almost nothing), is that using Dynamic Data Masking g is what you need. This way you can use the Dynamic Data masking function Random: this function on numeric column will return random value for users that does not have the permission to see the real value

    Check this Document to learn more about Dynamic Data masking

    For example:

    CREATE TABLE EMP (EMP_Id INT)  
    GO  
      
    INSERT EMP(EMP_Id) values (2),(4536),(86),(345)  
    GO  
      
    ALTER TABLE EMP   
    	ALTER COLUMN EMP_Id ADD MASKED WITH (FUNCTION = 'random(1,9999)');   
    GO  
      
    -- Create new user which will see only a fake values since he have no permission to see the real value  
      
    CREATE USER MaskingTestUser WITHOUT LOGIN;  
    GO  
    GRANT SELECT ON EMP to MaskingTestUser  
    GO  
      
    -- this is what the user will see (execute multiple times to test it)  
    EXECUTE AS USER = 'MaskingTestUser';    
    	SELECT * FROM EMP -- random value  
    REVERT  
    

  2. Bert Zhou-msft 3,436 Reputation points
    2022-03-24T02:59:06.083+00:00

    Hi,@Zaran

    Welcome to Microsoft T-SQL Q&A Forum!

    Please check this:

    Create table #temp  
    (  
     EMP_Id varchar(100) unique  
    )  
    Insert into #temp values(1020),(10245),(1200),(50300)  
    SELECT EMP_Id,CONVERT(nvarchar(10),LEFT(REPLACE(NEWID(),'-',''),8))AS FakeEMP_Id  
    FROM #temp  
    

    Here is result:
    186279-image.png
    Here's a link on random strings that I recommend you read.

    Best regards,
    Bert Zhou


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.