By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.30 |
CREATE TABLE testtable (
cardnumber VARCHAR(255));
INSERT INTO testtable VALUES
('1234-5678-9999-1234'),
('1234-567899-91234');
Records: 2 Duplicates: 0 Warnings: 0
SELECT cardnumber, CASE LENGTH(cardnumber)
WHEN 19 THEN
CONCAT(LEFT(REGEXP_REPLACE(cardnumber,'[0-9]','X'),15),
RIGHT(cardnumber,4))
WHEN 17 THEN
CONCAT(LEFT(REGEXP_REPLACE(cardnumber,'[0-9]','X'),13),
RIGHT(cardnumber,4))
END AS masked_cardnum
FROM testtable
| cardnumber | masked_cardnum |
|---|---|
| 1234-5678-9999-1234 | XXXX-XXXX-XXXX-1234 |
| 1234-567899-91234 | XXXX-XXXXXX-X1234 |
SELECT cardnumber,
CASE LENGTH(cardnumber)
WHEN 19 THEN CONCAT('XXXX-XXXX-XXXX-', RIGHT(cardnumber,4))
WHEN 17 THEN CONCAT('XXXX-XXXXXX-X', RIGHT(cardnumber,4))
END AS masked_cardnum
FROM testtable
| cardnumber | masked_cardnum |
|---|---|
| 1234-5678-9999-1234 | XXXX-XXXX-XXXX-1234 |
| 1234-567899-91234 | XXXX-XXXXXX-X1234 |