I need to write a SELECT statement that returns:

If the card number contains 16 digits, it should be displayed in this format: XXXX-XXXX-XXXX-1234, where 1234 are the actual last four digits of the number.

If the card number contains 15 digits, it should be displayed in this format: XXXX-XXXXXX-X1234.

I am stuck! Any help would be greatly appreciated! I am trying to use If then Replace Or Case When

CodePudding user response：

If you're using MySQL version that supports window function, I suggest using REGEXP_REPLACE() with LEFT() and RIGHT(). Here's an example:

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;

On this sample data:

CREATE TABLE testtable (
cardnumber VARCHAR(255));

INSERT INTO testtable VALUES
('1234-5678-9999-1234'),
('1234-567899-91234');

the query above will return the following result:

Or probably, don't even need to replace the first 12 or 11 digits. Just directly append the last 4 digits with XXX... This should work on any MySQL version:

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;

Demo fiddle

CodePudding user response：

Use LEFT(), SUBSTR(), RIGHT() to extract the parts of the string, and CONCAT_WS() to concatenate them together with a - delimiter.

SELECT CASE LENGTH(cardnumber)
    WHEN 16 THEN CONCAT_WS('-', LEFT(cardnumber, 4), SUBSTR(cardnumber, 5, 4), SUBSTR(cardnumber, 9, 4), RIGHT(cardnumber, 4))
    WHEN 15 THEN CONCAT_WS('-', LEFT(cardnumber, 4), SUBSTR(cardnumber, 5, 6), RIGHT(cardnumber, 5)
END AS formatted_cardnumber
FROM yourTable