Vigenere Cipher ala T-SQL

I started thinking about how this would work and got a bit obsessed...the Vigenere cipher is a very old cipher (but relatively secure for its time, with a resistance to basic frequency analysis) that uses a shared key and a Vigenere table, basically a grid of letters.

It uses a repeating pattern of your keyword as a sort of keystream, similar to a one time pad (which are superior, having no repeating pattern). Out comes the T-SQL hammer, because this looks like a nail to me:
DECLARE @TextToEncrypt VARCHAR(500) = 'This is an especially secret message that I wish to encrypt with a Vigenere cipher.';

--Strip out non-alpha chars
WHILE PATINDEX('%[^a-z]%', @TextToEncrypt) > 0
        SET @TextToEncrypt = STUFF(@TextToEncrypt, PATINDEX('%[^a-z]%',@TextToEncrypt),1,'');
--Set to upper case
SET @TextToEncrypt = UPPER(@TextToEncrypt);

--Shared secret key
DECLARE @Key VARCHAR(20) = 'YOUDNEVERGUESS';
--Let's make sure to "clean" that one too:
--Strip out non-alpha chars
WHILE PATINDEX('%[^a-z]%', @Key) > 0
        SET @Key = STUFF(@Key, PATINDEX('%[^a-z]%',@Key),1,'');
--Set to upper case
SET @Key = UPPER(@Key);

--Pad out key to length of cleartext
DECLARE @KeyPad VARCHAR(500);
SELECT @KeyPad=LEFT(REPLICATE(@Key,(LEN(@TextToEncrypt)/LEN(@Key))+1),LEN(@TextToEncrypt));

--Vigenere Cipher Encryption and Output
DECLARE @CipherText VARCHAR(500)='';
DECLARE @i INT = 1;
WHILE @i <= LEN(@TextToEncrypt)
BEGIN
        SET @CipherText = @CipherText +
                CHAR(65+((ASCII(SUBSTRING(@TextToEncrypt, @i, 1))-65+ASCII(SUBSTRING(@KeyPad, @i, 1))-65) % 26));
        SET @i=@i+1;
END

PRINT @CipherText
GO

The basic math for encryption is for each character (assuming 0-25 A-Z values), (plaintext + key) mod 26 = ciphertext. Output is thoroughly indecipherable, lacking the key (or a great deal of spare time): RVCVVWVRVYJIUAYZFBFIXVVZGIKKYUYWUEOMNOMLLGCBWULTOAZZBENAESHHEIXMGNYV

So to decrypt...my math is a bit shoddy but it is “theoretically” (ciphertext – key) mod 26 = plaintext. Something is going awry when cipher value is greater than key value, but anyway here’s the code with an IF/THEN workaround:
--Strike that...reverse it...
DECLARE @CipherText VARCHAR(500) = 'RVCVVWVRVYJIUAYZFBFIXVVZGIKKYUYWUEOMNOMLLGCBWULTOAZZBENAESHHEIXMGNYV';

--Shared secret key
DECLARE @Key VARCHAR(20) = 'YOUDNEVERGUESS';

--Pad out key to length of ciphertext
DECLARE @KeyPad VARCHAR(500);
SELECT @KeyPad=LEFT(REPLICATE(@Key,(LEN(@CipherText)/LEN(@Key))+1),LEN(@CipherText));

--Vigenere Cipher Decryption and Output
DECLARE @ClearText VARCHAR(500)='';
DECLARE @ClearChar CHAR(1);
DECLARE @i INT = 1;
WHILE @i <= LEN(@CipherText)
BEGIN
        --This is a cheating IF-THEN because I have something in the math not quite right...but she works
        IF (ASCII(SUBSTRING(@CipherText, @i, 1))>=ASCII(SUBSTRING(@KeyPad, @i, 1)))
                SET @ClearChar=CHAR(65+(((ASCII(SUBSTRING(@CipherText, @i, 1))-65)-(ASCII(SUBSTRING(@KeyPad, @i, 1))-65)) % 26));
        ELSE
                SET @ClearChar=CHAR(65+(26+(((ASCII(SUBSTRING(@CipherText, @i, 1))-65)-(ASCII(SUBSTRING(@KeyPad, @i, 1))-65)) % 26)));
        SET @ClearText = @ClearText + @ClearChar;
        SET @i=@i+1;
END

PRINT @ClearText;

Resulting output: THISISANESPECIALLYSECRETMESSAGETHATIWISHTOENCRYPTWITHAVIGENERECIPHER

There you are...it’s a very simple solution and not at all secure in a comparable way to modern computer-based symmetric algorithms, but kind of fun to understand and practice string manipulation with.