Step 1 - Encrypt a field
Select ENCRYPTBYPASSPHRASE('mickey mouse', cast(YourField as nvarchar(100))) as YourFieldEncrypted from table
NOTE1: If the field you are encrypting is a number field, you must cast it as a nvarchar as I did here.
NOTE2: If you case your field as a nvarchar, make sure you specify field size - "nvarchar(100)" not just nvarchar.
This will output your field as a varbinary field with a length of 8000.
Step 2 - To decrypt your field
Select convert(nvarchar(100),DECRYPTBYPASSPHRASE('mickey mouse',YourFieldEncrypted)) as YourFieldDecrypted from table
NOTE3: the "convert(nvarchar(100)" is necessary to convert your field from varbinary(8000) back to its original form.
OPTIONAL:
Step 4 - If you need to convert your encrypted field into a text field rather than a varbinaryfield:
Select convert(varchar(max), YourFieldEncrypted,1) as YourFieldEncryptedConvertedToVarchar from table
Step 5 - If you need to read your text field created in step 4 and decrypt it:
Select convert(nvarchar(100),DECRYPTBYPASSPHRASE('mickey mouse',convert(varbinary(max),YourFieldEncryptedConvertedToVarchar,1)))