masking the id of an employee in a select query

Issue

I have an ID which I want to mask the last 4 digits of that id.

example: my_id = 123456789

SELECT
concat(left(my_id,length(my_id) -4)) + ' ' + 'xxxx' AS masked_data
FROM
  dual ;

Expected output : 12345XXXX

But I am getting as 12345

Solution

The strings that you’re concatenating should all be arguments to the CONCAT() function. + is for addition, not concatenation.

SELECT
concat(left(my_id, length(my_id) - 4), 'xxxx') AS masked_data

You also don’t want a space before xxxx.

Answered By – Barmar

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0