Try This:
DECLARE @tbl TABLE ( StaffNumber varchar(10) )
INSERT INTO @tbl VALUES ( 'IP-1' )
INSERT INTO @tbl VALUES ( 'IP-9' )
SELECT TOP 1
CASE WHEN ISNUMERIC(SUBSTRING(StaffNumber,PATINDEX('%[0-9]%',StaffNumber),LEN(StaffNumber)))= 1 THEN
REPLACE
(
StaffNumber,
SUBSTRING(StaffNumber,PATINDEX('%[0-9]%',StaffNumber),LEN(StaffNumber)),
SUBSTRING(StaffNumber,PATINDEX('%[0-9]%',StaffNumber),LEN(StaffNumber))+1
)
ELSE
StaffNumber + '1' END AS NewstaffNumber,
SUBSTRING(StaffNumber,PATINDEX('%[0-9]%',StaffNumber),LEN(StaffNumber))+1
FROM @tbl ORDER by 2 desc
More Info:
http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=IncrementAlphaNumericValues[
^]