This might work for you.

PATINDEX is available in SQL Server 2005.

DECLARE @yourTable TABLE (someString varchar(100));

INSERT @yourTable

VALUES ('DDR Return PP12345'),('DDR Resturns PP12356'),('DDR Retunrs PP12367'), ('This one does not have a number'), ('With a number but no double P before it 128561');

SELECT someString,

s2 = RIGHT( someString, PATINDEX('%[^0-9]%', REVERSE(someString)) - 1)

FROM @yourTable