Replacing individual digits in a CustomerID field SQL Server

Question

I have some customer data that needs to be anonymised. I have customerIds which consists of numbers.

for example:

CustomerID
3937487

I need to swap each digit with an alternative, which should be enough for my requirement. Based on the following lookup table

Only issue I'm having is when I use the REPLACE function on the field:

REPLACE(REPLACE(CustomerID,2,9),9,6)

which gives me

CustomerID
3637487

It's swapping the digit 2 to a 9, then that same 9 to a 6. It needs to only replace the digits ONCE.

As I'm going to be changing millions of records in one go, using temp tables isn't possible from a performance perspective. Can this be done in one query, recursively?

Answer 1

I can't think of any way of accomplishing this in a single query. If I wanted to do this I'd create a function something along the lines of

CREATE FUNCTION [dbo].[AnonymiseId]
(
@Id [int]
)
RETURNS [int]
AS
BEGIN
    -- Declare the return variable here
    DECLARE @ResultVar int;

    DECLARE @substitutions nvarchar(10) = '7295380146';

    DECLARE @stringId nvarchar(100) = CONVERT(nvarchar(100), @Id);

    DECLARE @i int = 1

    DECLARE @substituteStringId nvarchar(100) = '';

    WHILE @i <= LEN(@stringID)
    BEGIN
        DECLARE @char nvarchar = SUBSTRING(@stringId, @i, 1);
        DECLARE @charValue int = CONVERT(int, @char);

        DECLARE @subsChar nvarchar = SUBSTRING(@substitutions, @charValue + 1, 1);

        SET @substituteStringId = CONCAT(@substituteStringId, @subsChar);

        SET @i = @i + 1
    END

    SET @ResultVar = CONVERT(int, @substituteStringId);

    -- Return the result of the function
    RETURN @ResultVar;

END
GO

and then just use it in the query

SELECT dbo.AnonymiseId(CustomerID) FROM ???