Willyanto Halim Willyanto Halim - 1 year ago 52
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
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 Source

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

      WHEN LENGTH(username) >= 6 THEN REPLACE(username, LEFT(username, 3), '***')
      ELSE CONCAT('***', username)
  )AS Username
FROM table
GROUP BY status
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download