Delete credit card numbers except last 4 digits

Hi, I have several tables that have a CCNumber field which has credit card numbers.

I would like to delete all but the last 4 digits.

Some of the fields are not 16 digits long, so I would only like to delete the last 4 digits of those records who have a ccNumber 16 digits long.

Any suggestions?
MichaelAragonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim P.Commented:
UPDATE MyTable
SET CCNumber = Right(CCNumber,4)
mbizupNerdCommented:
Try this:

UPDATE TableName
SET CCNumber = iif(len(ccNumber) = 16, CCNumber, right(CCNumber,4))
ArjiCommented:
UPDATE MyTable SET CCNumber = Right(CCNumber,4) WHERE Len(CCNumber) =16

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

mbizupNerdCommented:
Sorry!  I had that backwards.  (Always make a backup before running any action query)

Try this:

UPDATE TableName
SET CCNumber = iif(len(ccNumber) = 16,  right(CCNumber,4), CCNumber,)
Jim P.Commented:
I read the Q backwards. Arji and MBiz have it.
ArjiCommented:
mbizup,

I would remove that last , because I think it will create an error.  

SET CCNumber = iif(len(ccNumber) = 16,  right(CCNumber,4), CCNumber)
mbizupNerdCommented:
Thanks Arji :-)
I got an extraneous comma in there when I swapped the parameters around.
GRayLCommented:
Before you 'delete' those kind of numbers, you should always 'look' at what you will be doing before you trash the table.  Consequently, something like:

Select LName, CCNumber, Iif(Len(Trim(CCNumber))=16,Right(CCNumber,4),CCNumber) FROM myTable;

If there are any preceeding or trailing spaces you will need the Trim() function.
Leigh PurvisDatabase DeveloperCommented:
I agree with Ray - and more so really.  Do you really want to delete it?
Not just encrypt it somehow?
Gustav BrockCIOCommented:
> I would like to delete all but the last 4 digits.

> I would only like to delete the last 4 digits of those records ..

??

/gustav
Gustav BrockCIOCommented:
If you wish to scramble the card numbers, a common method is to replace blocks of digits with XXXX, typically the last four digits.

/gustav
Jim P.Commented:
Actually, if you look at most CC/Debit card reciepts nowdays only has the last 4 digits.  That way you can tell which card was used but without the first 12 digits and knowing when the card was issued it's practically impossible to know the rest of the card number without seeing it.
MichaelAragonAuthor Commented:
yeah... that was a typo.

All but the last 4 digits should be deleted.



there is no longer any need to store them in the database.
Jim P.Commented:
Then Arji has it.
GRayLCommented:
Arji has it so long as there are no leading or trailing spaces;-)

Else you need:

UPDATE myTable SET CCNumber=IIF(Len(Trim(CCNumber))=16, Right(Trim(CCNumber,4)),CCNumber);
stevbeCommented:
UPDATE myTable SET CCNumber = Right$(Trim(CCNumber,4))
Gustav BrockCIOCommented:
All credit card numbers have more than four digits and trailing spaces are not stored.
Thus, as there really is no need to update those records already updated:

  UPDATE
    MyTable
  SET
    CCNumber = Right(CCNumber, 4)
  WHERE
    Len(CCNumber) > 4;

/gustav
Jim P.Commented:
>> Some of the fields are not 16 digits long,

cactus_data,

Some companies that issue branded CC's (such as Sears did for years) that don't have the MC/Visa/... Logo don't use a 16 digit number.  Over the years Sears did get to 16 on their CC's but that was to match up with the Discover CC's (and others).

But there are probably still a few who do there own CC that don't have 16 Char's.
Gustav BrockCIOCommented:
I believe you. But I didn't mention 16 but 4 digits.
A card system with 3 digits only must be for a very exclusive customer base.

/gustav
Jim P.Commented:
From the original post:
>> so I would only like to delete the last 4 digits of those records who have a ccNumber 16 digits long.

He only want to hack it down on the full 16 digit CCNumbers.
ArjiCommented:
If CCNumber is a NUMBER then how can it have leading or trailing spaces in it? or any spaces at all?
Gustav BrockCIOCommented:
The count of digits doesn't matter as long as it is above 3.

/gustav
Jim P.Commented:
Arji,

Usually you store credit card numbers as a text field, just because trying to store it as a number runs into all sorts of problems.  Just like the US Tax Identification numbers (SSN).  Some states still have numbers that are like 024-55-1212.

Gustav,

From the original post:

>> Some of the fields are not 16 digits long, so I would only like to delete
>> the last 4 digits of those records who have a ccNumber 16 digits long.

He is stating he only want to edit the CC Numbers that are 16 digits long and ignore the rest.  So the request is that it is limited to the 16 digit CC Numbers. And if you do the left, right or mid of a number or string and there aren't enogh characters the functions then take all characters. As shown below:
-------------------------------------------
?right("123456789",4)
6789
?right("123",4)
123
Gustav BrockCIOCommented:
Michael corrected:

> All but the last 4 digits should be deleted.

But what is the problem? If for 16 digits numbers only:

  UPDATE
    MyTable
  SET
    CCNumber = Right(CCNumber, 4)
  WHERE
    Len(CCNumber) = 16;

/gustav
ArjiCommented:
jimpen,
thanks!  Never done credit card processing. All my systems are in-house MIS type stuff.  I guess I would have figured out that you can't seem to format a number properly to reflect correct data other than converting it to a string.  Yet another learning tidbit from this place....I love it!
MichaelAragonAuthor Commented:
hmmm...

arji
mbizup
and cactus_data
 all have working solutions to my question.....


where should the points go?
Gustav BrockCIOCommented:
Jimpen was first, but I would recommend it expanded with the where clause:

 WHERE
    Len(CCNumber) > 4;

to only update those records needing an update.

/gustav
ArjiCommented:
Actually, MBiz and I answered the question fully and was first at doing that (both at 4:49PM). Jimpen left out the WHERE clause.
Jim P.Commented:
Agreed.
mbizupNerdCommented:
Narrowing it down...
I stumbled into a nearly correct answer, which got much needed syntactical help from Arji :-)
I think Arji's response worked right off the cuff.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.