Multiple table-based replacement in a SQL function

Question

I need to create a function that performs multiple replacements in a string based on a lookup table. However, the result is always the last replacement as if the previous replacements don't happen. The identical stored procedure provides the correct result.

Here is my function:

CREATE FUNCTION tmp.fix_template_list_data_test 
    (@source varchar(max))
RETURNS nvarchar(max)
AS
BEGIN
    SELECT @source = REPLACE(@source, longer, shorter)
    FROM tmp.vaccine_replacements
    ORDER BY id

    RETURN @source
END

This is on SQL Server.

Answer 1

This works for me.

CREATE FUNCTION /*tmp*/dbo.fix_template_list_data_test( @source nvarchar(max) )
RETURNS nvarchar(max) WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS
BEGIN

    -- This FUNCTION makes a private copy of the replacements table to ensure ordering and to prevent taking excessive locks on the source table.
    DECLARE @replacements TABLE (
        Ordinal     int           NOT NULL PRIMARY KEY,
        ReplaceThis nvarchar(max) NOT NULL,
        WithThis    nvarchar(max) NOT NULL
    );

    INSERT INTO @replacements ( Ordinal, ReplaceThis, WithThis )
    SELECT
        ROW_NUMBER() OVER ( ORDER BY id ) AS Ordinal,
        longer  AS ReplaceThis,
        shorter AS WithThis
    FROM
        /*tmp*/dbo.vaccine_replacements;

    DECLARE @mutable nvarchar(max) = @source;

    DECLARE @i   int = 1;
    DECLARE @max int = ( SELECT MAX( Ordinal ) FROM @replacements );
    WHILE( @i <= @max )
    BEGIN
        SET @mutable = ( SELECT TOP 1 REPLACE( @mutable, ReplaceThis, WithThis ) FROM @replacements WHERE Ordinal = @i );
        SET @i = @i + 1;
    END;

    RETURN @mutable;
END;

Proof:

CREATE TABLE /*tmp*/dbo.vaccine_replacements (
    id int NOT NULL IDENTITY PRIMARY KEY,
    longer  nvarchar(max) NOT NULL,
    shorter nvarchar(max) NOT NULL
);

INSERT INTO /*tmp*/dbo.vaccine_replacements ( longer, shorter ) VALUES
( 'AAAAAA', 'A6' ),
( 'BBBBBB', 'B6' ),
( 'CCCCCC', 'C6' );

SELECT /*tmp*/dbo.fix_template_list_data_test( N'CCCCCCAAAAAABBBBBB' );

-- Outputs "C6A6B6"

The above FUNCTION uses CURSOR-style logic with a WHILE() loop - but that's okay because using WHILE is superior to a CURSOR for situations like these.