How to anonymize data in SQL?

Question

I need to anonymize a variable in SQL data (VAR NAME = "ArId").

The variable contains 10 numbers + 1 letter + 2 numbers. I need to randomize the 10 first numbers and then keep the letter + the last two numbers.

I have tried the rand() function, but this randomize the whole value.

SELECT TOP 1000 *
FROM [XXXXXXXXXXX].[XXXXXXXXXX].[XXXXX.TEST]

I have only loaded the data.

EDIT (from "answer"):

I have tried: UPDATE someTable SET someColumn = CONCAT(CAST(RAND() * 10000000000 as BIGINT), RIGHT(someColumn, 3))

However as i am totally new to SQL i don't know how to make this work. I put 'someColumn = new column name for the variable i am crating. RIGHT(someColumn) = the column i am changing. When i do that i get the message that the right function requires 2 arguments??

Example for Zohar: I have a variable containing for example: 1724981628R01On all these values in this variable i would like to randomize the first 10 letters and keep the last three (R01). How can i do that?

I'm sorry, but I don't understand the question. Please [edit] to include sample data as DDL+DML (create table and insert statement), your current attempt and desired results. Please note that we only need sample data that can demonstrate the problem, it doesn't have to be the actual data in your real project. A `declare @T as table (...).... insert into @T...` is enough in most cases. — Zohar Peled, Jun 18 '19 at 09:25
What do you mean with 'variable' here, are you not trying to update a table? — Joakim Danielson, Jun 18 '19 at 11:34

score 1 · Answer 1 · answered Jun 18 '19 at 11:02

A couple things. First, your conversion to a big int does not guarantee that the results has the right number of characters.

Second, rand() is constant for all rows of the query. Try this version:

UPDATE someTable
    SET someColumn = CONCAT(FORMAT(RAND(CHECKSUM(NEWID())
                                       ), '0000000000'
                                  ),
                            RIGHT(someColumn, 3)
                           );

How to anonymize data in SQL?

1 Answers1