Replace in SQL substring issue

Question

I would like to replace the all digits in a number with *, other than the last 4.

Here is my table:

Person
-----------
PersonID PersonBadgeNumber numeric(16,0)

Please post some example data, and before/after example, and correct grammar mistakes in question. (Clarify what "replace the all digits" means.) ty! — Nick Vaccaro
– Nick Vaccaro, Commented Jan 20, 2012 at 19:13

Yuck · Accepted Answer · 2012-01-20 19:19:59Z

2

Something like this will work...

DECLARE @badgeId Numeric(16, 0) = 1238985495;
SELECT
  REPLICATE('*', LEN(@badgeId) - 4) +
  RIGHT(@badgeId, 4);
-- Produces: ******5495

To use it in a query do this:

SELECT
  REPLICATE('*', LEN(PersonBadgeNumber) - 4) +
  RIGHT(PersonBadgeNumber, 4) RedactedBadgeNumber
FROM Person;

answered Jan 20, 2012 at 19:13

Yuck

51.1k13 gold badges108 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andriy M · Accepted Answer · 2012-01-20 23:10:33Z

0

Another method is to use STUFF():

DECLARE @n numeric (16, 0);
SET @n = 1234567890123456;
SELECT STUFF(@n, 1, LEN(@n) - 4, REPLICATE('*', LEN(@n) - 4))

The above returns:

---------------- 
************3456

answered Jan 20, 2012 at 23:10

Andriy M

78k18 gold badges100 silver badges157 bronze badges