These scripts can be used encrypt and decrypt data in a table. This can be used in columns with data integer and characters. This can be useful when you need to save your data in a format that no one can read. Only the DBA who knows the integer key value to return back can read the content back.
Function to encrypt data:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[encryptdata] (@firstname VARCHAR(50), @key INT) RETURNS VARCHAR(60) AS BEGIN DECLARE @firstname1 INT DECLARE @key2 VARCHAR(10) DECLARE @encryptdata VARCHAR(60) DECLARE @len INT SELECT @encryptdata = '' SELECT @len=LEN(@firstname) WHILE @len>0 BEGIN SELECT @firstname1 = ASCII(SUBSTRING(@firstname,@len,1)) SELECT @key2=@firstname1+@key SELECT @encryptdata = @encryptdata+CHAR(@key2) SELECT @len = @len-1 END RETURN(@encryptdata) END; GO
Function to decrypt data:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[decryptdata] (@firstname VARCHAR(50), @key INT) RETURNS VARCHAR(60) AS BEGIN DECLARE @firstname1 INT DECLARE @key2 VARCHAR(10) DECLARE @encryptdata VARCHAR(60) DECLARE @len INT SELECT @encryptdata = '' SELECT @len=LEN(@firstname) WHILE @len>0 BEGIN SELECT @firstname1 = ASCII(SUBSTRING(@firstname,@len,1)) SELECT @key2=@firstname1-@key SELECT @encryptdata = @encryptdata+CHAR(@key2) SELECT @len = @len-1 END RETURN(@encryptdata) END; GO
How to Use the script
CREATE TABLE #tmp(acno INT) INSERT #tmp VALUES('25469863') GO SELECT acno FROM #tmp
Normal Output:
acno
———–
25469863
Now we are going to encrypt the data 25469863 with the key value as 8 (this can be changed with your value)
SELECT dbo.encryptdata(acno,8) AS acno FROM #tmp
Encrpted Output:
acno
—————-
;>@A><=:
The value 25469863 has been encrypted to ;>@A><=:, hence once you decrypt this using decryptdata function you can get the original value.
select dbo.decryptdata(';>@A><=:',8) as acno
Decrypted Output“
acno
—————-
25469863
Leave a Reply