WITH
t0 AS (
SELECT
ship,
CHARINDEX(' ', ship) AS leftpos,
LEN(ship) - CHARINDEX(' ', REVERSE(ship)) + 1 AS rightpos
FROM outcomes
),
t1 AS (
SELECT
ship,
LEFT(ship, leftpos - 1) AS first,
SUBSTRING(ship, rightpos + 1, LEN(ship) - rightpos) AS last,
leftpos,
rightpos
FROM t0
WHERE leftpos > 0
AND rightpos < LEN(ship)
AND rightpos > leftpos
),
t2 AS (
SELECT
ship,
first,
last,
LEN(ship) - LEN(first) - LEN(last) - 2 AS middlelen
FROM t1
)
SELECT
ship,
first + ' ' + REPLICATE('*', middlelen) + ' ' + last AS starname
FROM t2
WHERE middlelen > 0
ORDER BY ship
CREATE TABLE Test_Trailing_Space2
(
num int NOT NULL,
name VARCHAR(10) NOT NULL,
PRIMARY KEY(num, name)
);
GO
INSERT INTO Test_Trailing_Space2 VALUES(1, 'John');
INSERT INTO Test_Trailing_Space2 VALUES(1, 'John ');
GOINSERT INTO Test_Trailing_Space2 VALUES(2, 'John ');
GO
SELECT *, LEN(name) len1,DATALENGTH(name) len2
FROM Test_Trailing_Space2;SELECT DISTINCT name
FROM Test_Trailing_Space2;
Как я понимаю, там бинарные операции только можно ... CHARINDEX вместо STRPOS , и снова неверно... 
Смысл задачи в этом и заключается TOP 1 WITH TIES альтернатива более тяжелой конструкции where goals in (select max(goals) from t)with t as (select g.team, (sum(l.goals)+ g.goals) as goals from Lineups as l
left join Games as g on g.game_id=l.game_id
group by g.game_id, g.team, g.goals,g.game_date)
select team, goals from t
where goals in (select max(goals) from t)
https://sql-ex.ru
Ваш запрос
Ошибка в запросе. Код: (UNKNOWN) ERROR: field position must be greater than zero
SPLIT_PART(ship, ' ', -1) --- Как поведет себя запрос если ship будет Null или состоять из одного пробела?
...
Интересно, решить последнюю задачу из обучающего этапа - дело принципа...