DOUBLE is not a valid data type in SQL

https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver16

Looking at this from a different angle ....

If you click on the upper left corner, it highlights all the cells.  That way you wont have a highlighted cell in the...

Try this

WHEN LTRIM(S1UPSP) LIKE 'RIOUS%' THEN '0'

or this

WHEN PATINDEX( '%RIOUS%', S1UPSP) > 0 THEN '0'

Is it possible that leading "spaces" are not char(32), but...

Something like this ...

CASE
  WHEN CHARINDEX(' ', S1UPSP) > 0 THEN '0' /* S1UPSP Contains spaces */
  WHEN LEN(S1UPSP) = 10 THEN RIGHT(S1UPSP, 8) /*...

You can read THIS ARTICLE on Splitter functions by Jeff Moden.

At the end of the article, you will find a link "The New Splitter Functions.zip" that will...

Perhaps a small tweak to Piet's 1st answer

SELECT src.*, v.val
FROM #t1 AS src
CROSS APPLY (SELECT val = COUNT(DISTINCT(NULLIF(RTRIM(x.val), '')))
        ...

I believe that this can be simplified as follows

CREATE TABLE #parts ( PartNumber   varchar(50)
           ...

I stand to be corrected, but the pattern appears to be as follows

• P1 is the only PartNumber that has PartValue in (1,2,3,4), therefor has its own UniqueNumber
• P2 and...

I gave you an example of how to use the OUTPUT clause, leaving you to modify it to work with your MERGE statement.

These links should help you further

Doing this with a simple UPDATE, you can construct an output as follows

CREATE TABLE #Employee (
    Emp       int...