Using Like with variables?
Your data follows a strict pattern:
If there is more than one entry like #TS%, then the first entry will be the one with #TS, the second will be the one with #TS - W. Obviously, if you sort descending, the order will be reversed.

Therefore the solution seems obvious: group by #TS, get those lines where row_number >1, when sorted descending.

SELECT     * , row_number() over (partition by left(TransmittalNumber,4) as RN order by TransmittalNumber DESC)
FROM         TransmittalSheet ts


To get only the multiples, you'd put that into a CTE:

With T1 as (SELECT     * , row_number() over (partition by left(TransmittalNumber,4) as RN order by TransmittalNumber DESC)
    FROM         TransmittalSheet ts)
select * from T1 where T1.RN&gt;1


You could then simply inner join that CTE to your original table on TransmittalID to remove all duplicates.

If the TransmittalNumber has a variable length, you'd also have to use patindex(), in order to calculate your parameter for the left() function.