Have a look at example:
DECLARE @RegistrationTable TABLE (Code VARCHAR(30))
INSERT INTO @RegistrationTable(Code)
VALUES('C0001'), ('C0099'), ('C0589')
SELECT SUBSTRING('C0000', 1, 5-LEN(IntCode)) + CONVERT(VARCHAR(4),IntCode) AS NewCode
FROM (
SELECT COALESCE(MAX(CONVERT(INT, RIGHT(Code,4))),0)+1 AS IntCode
FROM @RegistrationTable
) AS T
Returned value:
C0590
.
I hope it helps ;)