在sql查询中满足条件时，使用前三个字符的***显示字段的结果

Question

I have a table that look like this :

Username   |   Status
aaa        |   Pending
bbbbbbb    |   Pending
cccc       |   Cancelled
dddddddd   |   Cancelled
eeeeee     |   Approved
ffffff     |   Approved

the result that I'd like to show at the end is like this :

Status    | Username
Pending   | ***aaa, ***bbbb
Cancelled | ***cccc, ***ddddd 
Approved  | ***eee, ***fff

I've tried to do this select query

SELECT distinct status,CASE WHEN LENGTH(username) >=6 THEN GROUP_CONCAT(replace(username, left( username, 3 ) , '***') SEPARATOR ', ') ELSE GROUP_CONCAT('***',username SEPARATOR ', ') END AS Username FROM table group by status

However, the result of bbbbbbb from my query won't work because there is 3 characters username at first row. so, the result become like this :

Status    | Username
Pending   | ***aaa, ***bbbbbbb       // wrong (it should be ***aaa , ***bbbb)
Cancelled | ***cccc, ***ddddd // true
Approved  | ***eee, ***fff   // true

How do I perform the query to show the result that I expected?. thank you before

Answer 1

As I understand, you need to move CASE inside GROUP_CONCAT. Something like this:

SELECT DISTINCT status,
  GROUP_CONCAT(
    CASE
      WHEN LENGTH(username) >= 6 THEN REPLACE(username, LEFT(username, 3), '***')
      ELSE CONCAT('***', username)
    END SEPARATOR ', '
  )AS Username
FROM table
GROUP BY status