• The trick is that for a given concatenation of two strings using the + operator, the result will be truncated at 8000 bytes unless one of the operands has a MAX length (see the last paragraph in the "Remarks" section at https://docs.microsoft.com/en-us/sql/t-sql/language-elements/string-concatenation-transact-sql#remarks)

    The REPLACE you're highlighting is the only operand in all the concatenations you do that will have a MAX length (because the string_expression given as its first parameter is a MAX type, because you explicitly CAST it as such).

    So, basically, the output of all your concatenations is capped at 8000 bytes until you introduce the REPLACE.

    The later in the list of concatenations you introduce it, the more truncation you allow to occur.

    Just make sure you introduce a MAX type early in the list of concatenations and you'll be fine (most easily just CONVERT/CAST the first literal to a MAX type).

    Some code to illustrate (using a less efficient tally CTE just to keep things shorter):

    --Introducing the MAX type at the end saves us nothing, and we only get the first 8000 characters, the 'a' string
    WITH
    tally AS (SELECT TOP (32000) N=ROW_NUMBER() OVER (ORDER BY @@VERSION) FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2),
    string AS (SELECT string=REPLICATE('a',8000)+REPLICATE('b',8000)+REPLICATE('c',8000)+REPLICATE('d',8000)+CONVERT(VARCHAR(MAX),''))

    SELECT DISTINCT string_length=LEN(string), characters=SUBSTRING(string,N,1)
    FROM tally, string
    WHERE N<=LEN(string);

    --If I move the MAX type empty string up one spot, then the we'll get the 8000 'd' characters in addition to the 8000 'a' characters
    --because we didn't introduce a MAX type until after the 'b' and 'c' concatenations

    WITH
    tally AS (SELECT TOP (32000) N=ROW_NUMBER() OVER (ORDER BY @@VERSION) FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2),
    string AS (SELECT string=REPLICATE('a',8000)+REPLICATE('b',8000)+REPLICATE('c',8000)+CONVERT(VARCHAR(MAX),'')+REPLICATE('d',8000))

    SELECT DISTINCT string_length=LEN(string), characters=SUBSTRING(string,N,1)
    FROM tally, string
    WHERE N<=LEN(string);

    --Moving up one more spot, we'll get 'a','c', and 'd' characters

    WITH
    tally AS (SELECT TOP (32000) N=ROW_NUMBER() OVER (ORDER BY @@VERSION) FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2),
    string AS (SELECT string=REPLICATE('a',8000)+REPLICATE('b',8000)+CONVERT(VARCHAR(MAX),'')+REPLICATE('c',8000)+REPLICATE('d',8000))

    SELECT DISTINCT string_length=LEN(string), characters=SUBSTRING(string,N,1)
    FROM tally, string
    WHERE N<=LEN(string);

    --And finally, if I introduce it before any concatenation result would go over 8000 bytes, I get no truncation at all

    WITH
    tally AS (SELECT TOP (32000) N=ROW_NUMBER() OVER (ORDER BY @@VERSION) FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2),
    string AS (SELECT string=REPLICATE('a',8000)+CONVERT(VARCHAR(MAX),'')+REPLICATE('b',8000)+REPLICATE('c',8000)+REPLICATE('d',8000))

    SELECT DISTINCT string_length=LEN(string), characters=SUBSTRING(string,N,1)
    FROM tally, string
    WHERE N<=LEN(string);