Another way. And this will work for multiple trailing numbers or when there aren't any.

DECLARE @strings TABLE (LoginID VARCHAR(100))

INSERT INTO @strings

VALUES ('adventure-works\peter0'), ('Northwind\mary009'), ('Test\mark');

SELECT SUBSTRING(p, 1, ISNULL(NULLIF(PATINDEX('%[0-9]%',p),0),8000)-1)

FROM

(

SELECT p = SUBSTRING(LoginID,CHARINDEX('\', LoginID)+1,8000) FROM @strings

) prep;