Home > T-SQL > SQL SERVER – Caesar cipher stored procedure – Script Download – Encryption Algorithm

## SQL SERVER – Caesar cipher stored procedure – Script Download – Encryption Algorithm

Cryptography is the oldest technique, we were using in the past (before computer born) to secure our communication. Encryption is a methodology to convert the original readable text to unreadable format. Decryption is the reverse engineering of encryption.

In modern world, the cryptography has evolved drastically with mathematics, computer science, and electrical engineering. It help the encryption process more complex. So, It cannot (may be)  broken by the third-party.

In this blog post, I am going to introduce two stored procedures that encrypt the plain text and other procedure decrypt the encrypted text. I am using caesar’s ciper technique. It is a type of substitution cipher in which each letter in the plaintext is replaced by a letter some fixed number of positions down the alphabet -Wikipedia.

Encryption Stored procedure: CCEncrypt

— =============================================
— Author:        Ayyappan Thangaraj, SQLServerRider.com
— Create date: 1/22/2013
— Description:    Caesar Encryption algorithm
— =============================================
Create PROCEDURE SP_CCEncrypt
@Ptext as varchar(500)
AS
BEGIN
SET NOCOUNT ON;
declare @SHIFTNO as tinyint = 3
declare @Etext as varchar(500) =”
declare @pc as varchar(1)
Declare @i as smallint = 1
Declare @n as smallint
set @n = len(@Ptext)
set @Ptext = upper(@Ptext)
while @i < = @n
BEGIN
set @pc = SUBSTRING(@Ptext, @i, 1)
if ascii(@pc) between 65 and 90
if ascii(@pc)+@SHIFTNO > 90
set @pc = char((ascii(@pc)+@SHIFTNO)-90+64)
else
set @pc = char((ascii(@pc)+@SHIFTNO))
set @Etext = @Etext + @pc
Set @i = @i + 1
END
select @Etext
END
GO

Example:

Decryption Stored Procedure: CCDecrypt

— =============================================
— Author:        Ayyappan Thangaraj, SQLServerRider.com
— Create date: 1/22/2013
— Description:    Caesar Decryption algorithm
— =============================================
Create PROCEDURE SP_CCDecrypt
@Etext as varchar(500)
AS
BEGIN
SET NOCOUNT ON
declare @SHIFTNO as smallint = -3
declare @Ptext as varchar(500) =”
declare @Ec as varchar(1)
Declare @i as smallint = 1
Declare @n as smallint
set @n = len(@Etext)
set @Etext = upper(@Etext)
while @i < = @n
BEGIN
set @Ec = SUBSTRING(@Etext, @i, 1)
if ascii(@Ec) between 65 and 90
if ascii(@Ec)+@SHIFTNO < 65
set @Ec = char(91-(65-(ascii(@Ec)+@SHIFTNO)))
else
set @Ec = char((ascii(@Ec)+@SHIFTNO))
set @Ptext = @Ptext + @Ec
Set @i = @i + 1
END
select lower(@Ptext)
END
GO

Example:
Categories: T-SQL
1. July 21, 2014 at 2:05 am

2. May 25, 2016 at 6:02 am

Thank you very much.

MfG
Manuel M.

3. May 25, 2016 at 6:06 am

Thank you this code was very helpful.
Hope to see other nice Code Parts in the nearer future

Yours,
Manuel M.

4. May 25, 2016 at 6:10 am