SQL

 

table variable, ENCRYPTBYPASSPHRASE, DECRYPTBYPASSPHRASE

declare
     @key varchar(50) = 'fGsltwtHgHobStwbiHsnpbhelj316'
    , @cardNumber varchar(max) = '537153001234567890'
   
declare @creditCards table (
    ownerId bigint
    , cardNumber varbinary(max)
)

insert into @creditCards
    (ownerId, cardNumber)
select 1, ENCRYPTBYPASSPHRASE (@key, @cardNumber )

select
    ownerId
    , cardNumber as 'encryptedCardNumber'
    , convert (varchar(max), DECRYPTBYPASSPHRASE (@key, cardNumber ) ) as 'decryptedCardNumber'
from @creditCards

OutputownerId    encryptedCardNumber    decryptedCardNumber
1          0x01000000A507E0A9A    537153001234567890
           45A5C80E46734197C4D
           BA44929625CC11A754F
           618086BD4DE4A976D84
           A8ACFDDB71D5A3