You can use syntax working with all versions of SQL Server like this. It's a little bit messy, but should work on any version starting with 2005 at least. I'm using CTE just for demo to create virtual table.

with tmp as (
    select 'INV PNI S000060803 1' as c
    union all select 'INV PNI  P000035365 132'
    union all select 'INV PRVC  S000059468 4'
    union all select 'INV PIV P000034785 2'
    union all select 'INV PIV  T000034785 27'
)
select SUBSTRING(REVERSE(SUBSTRING(REVERSE(c), 0, CHARINDEX(' ', REVERSE(c), CHARINDEX(' ', REVERSE(c)) + 1))), 0, 11)
from tmp

Please try the following solution based on the PARSENAME() function use.

A minimal reproducible example is not provided. 😒

An assumption is made that the tokens column has just a single space character between words/tokens.

SQL #1

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, tokens VARCHAR(256));
INSERT @tbl (tokens) VALUES
('INV PNI S000060803 1'),
('INV PNI P000035365 132'),
('INV PRVC S000059468 4'),
('INV PIV P000034785 2'),
('INV PIV T000034785 27');
-- DDL and sample data population, end

SELECT t.* 
    , PARSENAME(c, 2) AS result
FROM @tbl AS t
    CROSS APPLY (SELECT REPLACE(tokens, SPACE(1), '.')) AS t1(c);

SQL #2

Implementation when there are multiple spaces between words/tokens.

SELECT t.* 
    , PARSENAME(c, 2) AS result
FROM @tbl AS t
CROSS APPLY  (SELECT TRY_CAST('<r><![CDATA[' + tokens + ']]></r>' AS XML).value('(/r/text())[1] cast as xs:token?','VARCHAR(MAX)')) AS t1(x)
CROSS APPLY (SELECT REPLACE(x, SPACE(1), '.')) AS t2(c);

Output

Late answer, but here is another option

Select A.ID
      ,NewVal = reverse(JSON_VALUE(JS,'$[1]'))
 From  YourTable A
Cross Apply (values ('["'+replace(string_escape(reverse( YourColumn ),'json'),' ','","')+'"]') ) B(JS)