SQL Server Function to Encrypt Integer Values
By: Eli Leiba | Updated: 2017-02-03 | Comments (3) | Related: > Encryption
You need a simple and fast tool to encrypt and decrypt integer values without having to deal with complex encryption keys, certificates and other complex encryption methods. In this tip we cover a simple approach to encrypt integers.
I coded two very short and simple T-SQL functions that do integer encryption and decryption. The algorithms used are very simple and implement a simple arithmetic encryption algorithm that encrypts an integer value to a string using the one of these values (ABCDEFGHIJKLMNOPQRSTUVWXYZ).
The encryption algorithm:
- Takes the modulo of the given integer number by 26 (the length of alphabet)
- Finds the letter in the alphabet that in the position of the result and concatenates it to the result string
- Divide the given number by 26 and continue with only the integer result
- Continue steps 1,2,3 until the division result is 0
- The resulting string is encrypted value
The decryption algorithm:
- Take the given string and reverse it
- Loop through all the characters of the string, from 1 to the length of the encrypted string
- Find the position of that letter in the alphabet and
multiply it by the power of 26 by the current index letter in the given string
- Accumulate the results for each value into the decrypted number result
- After the loop is finished the decrypted value is returned
the T-SQL code:
CREATE FUNCTION dbo.Encrypt2String (@Number2Encrypt INT) RETURNS VARCHAR(30) AS BEGIN DECLARE @AlphabetCode VARCHAR(26) DECLARE @AlphabetLength INT = 26 DECLARE @EncryptedString VARCHAR(30) = '' DECLARE @CurrDigitInNumber CHAR(1) SET @AlphabetCode = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' SET @EncryptedString = SUBSTRING(@AlphabetCode, (@Number2Encrypt % @AlphabetLength) + 1, 1) WHILE @Number2Encrypt > 0 BEGIN SET @CurrDigitInNumber = SUBSTRING(@AlphabetCode, ((@Number2Encrypt / @AlphabetLength) % @AlphabetLength) + 1, 1) SET @Number2Encrypt = @Number2Encrypt / @AlphabetLength IF @Number2Encrypt != 0 SET @EncryptedString = @CurrDigitInNumber + @EncryptedString END RETURN @EncryptedString END GO
CREATE FUNCTION dbo.Decrypt2Number (@encryptString VARCHAR(7)) RETURNS INT AS BEGIN DECLARE @AlphabetCode VARCHAR(26) DECLARE @AlphabetLength INT = 26 DECLARE @Idx INT = 0 DECLARE @Letter CHAR(1) DECLARE @LetterValue INT DECLARE @ReversedEncryptString VARCHAR(7) DECLARE @DecryptedNumber INT = 0 SET @ReversedEncryptString = REVERSE(@encryptString) SET @AlphabetCode = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' WHILE @Idx < LEN(@encryptString) BEGIN SET @Letter = SUBSTRING(@ReversedEncryptString, @Idx + 1, 1) SET @LetterValue = (CHARINDEX(@Letter, @AlphabetCode) - 1) * POWER(@AlphabetLength, @Idx) SET @DecryptedNumber = @DecryptedNumber + @LetterValue SET @Idx = @Idx + 1 END RETURN @DecryptedNumber END GO
Here is an example of how to use these functions.
The result is: DSYQ
The result is: 65536
These functions were tested using SQL Server 2012 and 2014 Developer edition, but should work on all versions and editions.
- You can modify the order of the letters in the @AlphabetCode string so the values are not in order like ABCDEFG...
- You can add other characters into the @AlphabetCode string such as numbers or other characters
- Use this to scramble IDs, SSNs, etc., but note that leading zeroes are removed when the value is decrypted.
- Use these functions like any other SQL Server function.
About the author
View all my tips
Article Last Updated: 2017-02-03