MS-SQL - Encryption and Decryption in MS-SQL

In this article, a SQL Server user-defined function is created that will encrypt any input string and decrypt the encrypted string.

Let see how to create enceypt and decrypt user defined function.

Step 1
Create a Encryption user defined function,it is look like this

CREATE FUNCTION Dbo.Encryption(@Value as NVARCHAR(100))
RETURNS NVARCHAR(100)
AS

    BEGIN
        
        DECLARE @EncryptedValue NVARCHAR(100)
        DECLARE @Index INT
        DECLARE @Increment INT
        
         SET @EncryptedValue = ''
         SET @Index = 1
         SET @Increment = 128
         
        
        WHILE @Index <= LEN(@Value)
            BEGIN
                SET @EncryptedValue = @EncryptedValue + 
                                NCHAR(ASCII(SUBSTRING(@Value, @Index, 1)) +
                                @Increment + @Index - 1)
                 SET @Index = @Index + 1
            END
        
         RETURN @EncryptedValue
        
    END 
GO

Step 2
Create a Decryption user defined function,it is look like this

CREATE FUNCTION dbo.Decryption(@EncrypteValue NVARCHAR(100))
RETURNS NVARCHAR(100)
AS
    BEGIN
        
        DECLARE @DecrypteValue NVARCHAR(100)
        DECLARE @Index INT
        DECLARE @Increment INT
        
         SET @DecrypteValue = ''
         SET @Index = 1
         SET @Increment = 128
         
         WHILE @Index <= LEN(@EncrypteValue)
            BEGIN
                SET @DecrypteValue = @DecrypteValue + 
                        CHAR(UNICODE(SUBSTRING(@EncrypteValue, @Index, 1)) - 
                        @Increment - @Index + 1)
                SET @Index = @Index + 1
            END
         
         RETURN @DecrypteValue
         
         
    END
Go

Step 3
Calling above user define functions, it is look like this

DECLARE @value NVARCHAR(100)
SET @value='MS-SQL' 

DECLARE @EncrypteValue NVARCHAR(100)
SELECT @EncrypteValue=dbo.Encryption(@value)

SELECT dbo.Encryption(@value) as Encryption,dbo.Decryption(@EncrypteValue) as Decryption

Output

Download
Download Script