SQL Encrypt/Decrypt numbers

Question

I wonder if that is possible in TSQL

I know vigenere (cipher) encryption can encrypt values like "Hello, How are you" to become "Hfnlp, Jox crf aov" but the problem is that it keeps the numbers un-encrypted "1234567" will remain "1234567"

any idea how to encrypt everything including numbers and to have it as Function/Procedure in SQL Server?

Answer 1

I have build the script myself

To Encrypt

ALTER FUNCTION [dbo].[fnEncrypt] 
(
    @Str varchar(max),
    @key varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
--Akram Mustafa
declare @i int = 1
declare @x int = 0
declare @Result varchar(max) = ''
declare @List varchar(100) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
declare @TempKey varchar(max) = @key

WHILE LEN(@TempKey) < LEN(@Str)
BEGIN
    SET @TempKey = @TempKey + @key 
END

SET @key = @TempKey

WHILE (@i <= LEN(@Str)) 
BEGIN
    IF (CHARINDEX(SUBSTRING(@Str, @i, 1), @List) > 0)
    BEGIN
        SET @x = ((CHARINDEX(SUBSTRING(@Str, @i, 1), @List) +  CHARINDEX(SUBSTRING(@key, @i, 1), @List)) % LEN(@List)) + 1
        SET @Result = @Result + SUBSTRING(@List, @x, 1)
    END
    ELSE
    BEGIN
        SET @Result = @Result + SUBSTRING(@Str, @i, 1)
    END
    SET @i = @i + 1
END

RETURN @Result
END

To Decrypt

ALTER FUNCTION [dbo].[fnDecrypt] 
(
    @Str varchar(max),
    @key varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
--Akram Mustafa
declare @i int = 1
declare @x int = 0
declare @Result varchar(max) = ''
declare @List varchar(100) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
declare @TempKey varchar(max) = @key

WHILE LEN(@TempKey) < LEN(@Str)
BEGIN
    SET @TempKey = @TempKey + @key 
END

SET @key = @TempKey

WHILE (@i <= LEN(@Str)) 
BEGIN
    IF (CHARINDEX(SUBSTRING(@Str, @i, 1), @List) > 0)
    BEGIN
        SET @x = ((CHARINDEX(SUBSTRING(@Str, @i, 1), @List) -  CHARINDEX(SUBSTRING(@key, @i, 1), @List) + LEN(@List) - 1) % LEN(@List))
        IF @x = 0
        BEGIN
            SET @x = LEN(@List)
        END
        SET @Result = @Result + SUBSTRING(@List, @x, 1)
    END
    ELSE
    BEGIN
        SET @Result = @Result + SUBSTRING(@Str, @i, 1)
    END
    SET @i = @i + 1
END

RETURN @Result
END