The problem of SELECT assignment and order by conflict

Problem Description:
By using the SELECT statement, polling the data in the table, and processing the variable data, if there is an order by statement, the desired result is not obtained, but the order by is removed and the result is normal.
Specific problem performance refer to the following questions to reproduce the code

Problem Recurrence Code
--Test data
DECLARE @T TABLE (id int,value nvarchar (16))
INSERT into @T SELECT
1, N ' Good man ' UNION all SELECT
2, N ' bad guys ' UNION all SELECT
3, N ' eat ' UNION all SELECT
4, N ' rubbish '

--Assigning value processing
DECLARE @str nvarchar (4000)
SET @str = N ' I'm not a good person, not a garbage '
SELECT @str = REPLACE (@str, value, n ' <u> ' + value + N ' </u> ')
From @T
WHERE CHARINDEX (value, @str) > 0
--order by CHARINDEX (value, @str) DESC
SELECT @str

/*-Results (when an assignment-processing statement comments out an ORDER by)
I am not a <u> good person </u> and not <u> garbage </u>
-- */

/*-Results (when an assignment-processing statement adds an order by)
I am not a <u> good person </u>
-- */

Problem Analysis:
The results of two processing statements are different, and by looking at their execution plans, you should see why.
SET Showplan_all on
Outputs two execution plans for executing statements (only the Stmttext section, where interested readers can learn about other parts when testing on their own computers)

Stmttext	Step
DECLARE @str nvarchar (4000) SET @str = N ' I'm not a good person, not a garbage '
SELECT @str = REPLACE (@str, value, n ' <u> ' + value + N ' </u> ') from @T WHERE CHARINDEX (value, @str) > 0	4
	|--	Compute Scalar (DEFINE: [Expr1002]=replace ([@str], @t.[value], ' <u> ' +@t.[value]+ '))	3
		|--	Filter (WHERE: (CHARINDEX (@t.[value), [@str], NULL) >0)	2
			|--	Table Scan (OBJECT: (@T))	1
DECLARE @str nvarchar (4000) SET @str = N ' I'm not a good person, not a garbage '
SELECT @str = REPLACE (@str, value, n ' <u> ' + value + N ' </u> ') from @T WHERE CHARINDEX (value, @str) > 0 O Rder by CHARINDEX (value, @str) DESC	5
	|--	Sort (Order BY: ([Expr1003] DESC))	4
		|--	Compute Scalar (DEFINE: [Expr1002]=replace ([@str], @t.[value], ' <u> ' +@t.[value]+ ' </u> '), [expr1003]= CHARINDEX (@t.[value], [@str], NULL))	3
			|--	Filter (WHERE: (CHARINDEX (@t.[value), [@str], NULL) >0)	2
				|--	Table Scan (OBJECT: (@T))	1

when there is an ORDER BY clause, for the Select @str = for this assignment, SQL Server considers that the assignment processing will only retain the processing result of the last record, and the ordered by clause determines the data sequence, You know that the last record is that, so only the last record of the order by is processed. (Readers can test by themselves, adjust the order by sequence to see if the result matches my inference)
when there is no ORDER BY clause, because the data sequence cannot be determined, SQL Server must scan each piece of data that satisfies the condition to get the result. So each scan of a record is processed once, so the result is the
 
Problem resolution that we predicted:
Modify the processing statement so that the query optimizer uses the execution method that is consistent with the results we need to solve the problem.
for the processing statements in the example, you can adjust the following:
DECLARE @str nvarchar (4000)
SET @str = N ' I am not a good person, nor garbage '
SELECT @str = REPLACE (@str, Value, n ' <u> ' + value + N ' </u> ')
from (
    SELECT top PERCENT
         value
    from @T
    WHERE CHARINDEX (value, @str ) > 0
    Order by CHARINDEX (value, @str) DESC
) A
SELECT @str
 
Add:
The conclusion of this question is only The author of the query analysis of a deduction, there is no corresponding official documents can be proved, so you are welcome to express their views