Tuesday, October 15, 2013

Random values in Select Query to hide information

There are situations when manager or BA want you to hide certain information in a report, there are a lot of programming ways to achieve this, for laziness it is also doable in SQL.

E.g. I have a list of sensitive customer name and their sales figure, I would like to show the other party how many sales I made, however hiding all the names.

the idea came to my mind is, using a string 'Customer' and a random number from 1-10 combine to get a random name such as 'Customer5', etc.

syntax:

 'Customer' + CAST((CAST(RAND(CHECKSUM(NEWID())) * 10 as INT) + 1) as varchar(2))

RAND(Checksum(NEWID()))
Above provides a random number between 0 - 1, use it as root times 10 to get a random number between  1- 10. hence end up having 'Customer1' - 'Customer10' in the selection list.