Delete credit card numbers except last 4 digits

UPDATE MyTable
SET CCNumber = Right(CCNumber,4)

Try this:

UPDATE TableName
SET CCNumber = iif(len(ccNumber) = 16, CCNumber, right(CCNumber,4))

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,)

I read the Q backwards. Arji and MBiz have it.

mbizup,

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

SET CCNumber = iif(len(ccNumber) = 16,  right(CCNumber,4), CCNumber)

Thanks Arji :-)
I got an extraneous comma in there when I swapped the parameters around.

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.

I agree with Ray - and more so really.  Do you really want to delete it?
Not just encrypt it somehow?

> 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

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

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.

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.

Then Arji has it.

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);

UPDATE myTable SET CCNumber = Right$(Trim(CCNumber,4))

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

>> 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.

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

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.

If CCNumber is a NUMBER then how can it have leading or trailing spaces in it? or any spaces at all?

The count of digits doesn't matter as long as it is above 3.

/gustav

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

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

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!

hmmm...

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


where should the points go?

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

Actually, MBiz and I answered the question fully and was first at doing that (both at 4:49PM). Jimpen left out the WHERE clause.

Agreed.

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.