A simpler truncation function that renders credit card numbers unreadable

[Serman D.]

Background: The Payment Card Industry (PCI) Data Security Standard
(PCI DSS) is a standard for financial institutions. It requires
sensitive information, such as credit card numbers, to be "unreadable
anywhere it is stored" using hashing, truncation or encryption.

I am looking for a simple truncation function that replaces the last
four digits in the given numeric with four characters (e.g. '*'). As
as Perl programmer (where a simple "s/.{4}$/****/" would suffice), the
resulting SQL/PL code strikes me as unnecessarily complex. Is there a
simpler way?

-- Replace last four characters in decimal number with stars.
-- E.g. 1234567890123456 -> 123456789012**** .
CREATE FUNCTION FUNC_MASK_LAST_4 (cardno decimal(21,0))
RETURNS CHAR(32)
NO EXTERNAL ACTION
DETERMINISTIC
RETURN SUBSTR(STRIP(CAST(cardno AS CHAR(32)), T, ' '),
32 - LENGTH(STRIP(CAST(cardno as char(32)), l,
'0')) + 1,
CAST(CEIL(LOG10(cardno)) AS INT) - 4) || '****'
@

create table largenum (lval decimal(21,0) not null)
@

insert into largenum values
(1234567890123456789),
( 123456789012345678),
( 12345678901234567),
( 1234567890123456),
( 123456789012345),
( 12345678901234),
( 1234567890123),
( 123456789012),
( 12345678901),
( 1234567890),
( 123456789),
( 12345678),
( 1234567),
( 123456),
( 12345),
( 1234)
@

select lval, FUNC_MASK_LAST_4(lval) as masked from largenum
@
LVAL MASKED
----------------------- --------------------------------
1234567890123456789. 123456789012345****
123456789012345678. 12345678901234****
12345678901234567. 1234567890123****
1234567890123456. 123456789012****
123456789012345. 12345678901****
12345678901234. 1234567890****
1234567890123. 123456789****
123456789012. 12345678****
12345678901. 1234567****
1234567890. 123456****
123456789. 12345****
12345678. 1234****
1234567. 123****
123456. 12****
12345. 1****
1234. ****

16 record(s) selected.

https://www.pcisecuritystandards.org/pdfs/pci_dss_v1-1.pdf

Regards,
Serman D.
--

[Serge Rielau]

CREATE FUNCTION FUNC_MASK_LAST_4 (cardno decfloat(34))

RETURNS CHAR(32)
NO EXTERNAL ACTION
DETERMINISTIC

RETURN SUBSTR(TRIM(CHAR(cardno)), 1, LENGTH(TRIM(CHAR(cardno))) - 4) ||
'****'
@

Prereq DB2 9.5 (DECFLOAT)
Otherwise you coulf go to BIGINT iff you can limit yourself to 18 digits

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab