Use this function for very simple encryption/decryption
CREATE FUNCTION dbo.fnSimpleEncDec
(
@StringText VARCHAR(8000),
@PasswordCharacter CHAR(1)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Index SMALLINT,
@ReturnText VARCHAR(8000)
SELECT @Index = DATALENGTH(@StringText),
@ReturnText = ''
WHILE @Index > 0
SELECT @ReturnText = CHAR(ASCII(SUBSTRING(@StringText, @Index, 1)) ^ ASCII(@PasswordCharacter)) + @ReturnText,
@Index = @Index - 1
RETURN @ReturnText
END
To encrypt the fields, write
UPDATE MyTable
SET first_name = dbo.fnSimpleEncDec(first_name, '?'),
last_name = dbo.fnSimpleEncDec(last_name, '*')
To decrypt the fields, write
UPDATE MyTable
SET first_name = dbo.fnSimpleEncDec(first_name, '?'),
last_name = dbo.fnSimpleEncDec(last_name, '*')
You can use whatever character you like as password. There is one caveat or drawback! You can't use a password character that normally exist in the field, such as 'e'. If you do, SQL server truncates the string at the position where field contains same character as password character used.
For names, I would prefer using
SELECT dbo.fnSimpleEncDec(first_name, CHAR(31))
because then I will XOR 5 bits out of 8 for every character in the text to be encrypted or decrypted! And the text still looks somewhat normal.
|
