Mask credit card numbers in SQL Server

I have a column in my database that stores a credit card number. After keeping it in the database for about t开发者_StackOverflow社区wo weeks, I want to be able to run a query to update all of the credit card entries and mask them - only showing the last four digits. So far I have been unsuccessful at finding a way to go about this - what if the credit card numbers vary in length? It would be better if I could mask the first 12 digits unless it starts with a 3, then mask only the first 11.

You have to restructure your handling of payment (credit) card data, and immediately. Otherwise, your company could lose the ability to process payment cards, and you'll then probably get fired. And if these plain-text numbers get compromised by an un-authorized source ...

First, you'll want to encrypt your payment card names, numbers, and expiration dates. Then, you'll want to take a database backup, put it on tape, and get it off-site. Then, purge all existing database backups, because these contain plain-text credit card numbers - a big no-no according to the Payment Card Industry.

The safest thing to do would be to store the four right-most digits in another encrypted column in your table. Then, you only need to select this field, and decrypt it on the client end (remember, you have to make sure payment card numbers are encrypted while going "over the wire" [a.k.a. your LAN or WAN or internet connection]). Display the number as "Account ending in ####" or something similar.

The easy answer is:

UPDATE dbo.MyLittleSecurityBreach 
    SET CreditCardNumber = RIGHT(CreditCardNumber, 4);

Don't bother storing the * mask characters in the database, what good is that? If you have the type, you know that Amex is 15 characters, and all others are 16. Add the 11 or 12 mask characters at presentation time.

That all said, if you're not listening to the advice to encrypt this data, I highly recommend you advise your employer that you need someone to help with data security. This is a disaster waiting to happen.