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 .