Or just put it in the SELECT:

DECLARE @ReturnString nvarchar(max) = 'Flat 24 Loddon House';
SELECT @ReturnString = SUBSTRING(
        STUFF(LTRIM(@ReturnString),1,LEN(Candidate),'') -- part
    ,   PATINDEX('%[A-Z]%',@ReturnString),LEN(@ReturnString) -- number
        )
FROM tbl_Matrix_SAONParts
WHERE @ReturnString LIKE Candidate + '%';
SELECT @ReturnString;

John