More efficient/cleaner way of doing a select that traverses a specific column?
A recursive CTE will find that for you.  By adding in the desc and TOP 1 it will look for the longest possible path.  If there were two paths it could follow it will pick one based on the ORDER statement

;WITH follow(idstart, idend, oldurl, target, depth, path) as 
(
    SELECT S.id idstart, S.id idend, S.oldurl, S.target, 1 AS depth, cast(S.oldurl as varchar) AS path
    FROM mytable as S
    UNION ALL
    SELECT S2.id idstart, p.idend idend, S2.oldurl, p.target, p.depth + 1 AS depth, cast(RTRIM(p.path) + '-&gt;' + S2.oldurl as varchar) AS path
    FROM follow AS p JOIN mytable as S2 on S2.target = p.oldurl
    WHERE p.target is not null
)
SELECT TOP 1 * FROM follow
WHERE idstart=1
ORDER BY depth desc;