Willyanto Halim Willyanto Halim - 5 months ago 10
SQL Question

display result of field using *** at first 3 characters when condition is met in sql query

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

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