CODEWITHSUNDEEP
sqlsql-server-2008replace
shenn
shenn

Reputation: 887

Replace in SQL substring issue

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)

Upvotes: 0

Views: 359

Answers (2)

Andriy M
Andriy M

Reputation: 77737

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

Upvotes: 0

Yuck
Yuck

Reputation: 50865

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;

Upvotes: 2

Related Questions