Obfuscate decimal(6,2) number using T-SQL

Question

How can I effectively obfuscate numbers from range 0.00 ~ 9999.99 like

• 11.68
• 6.84
• 7.99
• 7.00

so that small difference in values of similarily-sized numbers is not immediately visible?

I thought about splitting each number into integer and decimal part and use them as coordinates (e.g. 11.68 → (11, 68)) and converting them to polar coords, which is nice, but I still need two values to store them.

Do you know some nice tranformation of decimal(6,2) into another single number (can be like float or even int) and back which relies only on T-SQL math functions?

---

I would like to add one more barrier between values and people who have full database acceess and can be tempted to analyze true (unobfuscated) values they see, but are relatively lazy to learn T-SQL and study how "nonsense values" they see after obfuscation, can be decoded.

Answer 1

Use ENCRYPTBYPASSPHRASE() and DECRYPTBYPASSPHRASE()

Suitable methods are already present since SQL Server 2008. Their purpose is encryption, but well, with locally stored passphrase it's merely just an obfuscation :) (Anyway, at the moment this is what was expected in the question.)

DECLARE @Value decimal(6,2) = 7.99;
DECLARE @Passphrase varchar(4) = 'abcd';

-- obfuscation
DECLARE @Obf varbinary(50) = ENCRYPTBYPASSPHRASE(@Passphrase, CAST(@Value AS varchar(20)));

-- deobfuscation
SELECT CAST(CAST(DECRYPTBYPASSPHRASE(@Passphrase, @Obf) AS varchar(20)) AS decimal(6,2)) 

Value obfuscated this way can be also held in varchar/nvarchar data type.