Comments by Radhadeep12 (Top 3 by date)

Adarsh has solved your problem.

Code given by me also works. Just need to convert it to char or varchar

SET IDENTITY_INSERT Increment ON
INSERT Increment(Id,Val) VALUES(12345,'AB')
SET IDENTITY_INSERT Increment OFF

SELECT * FROM Increment

ID Val
1 AB
2 CD
3 EF
12345 AB

SELECT
CASE
WHEN LEN(Id) < 2 THEN CONVERT(CHAR(5),REPLACE(STR(Id,3),' ',0))
ELSE CONVERT(CHAR(5),ID)
END AS Id, Val FROM Increment

Id Val
001 AB
002 CD
003 EF
12345 AB

You can use Case construct to apply condition as per your need as below:-

SELECT
CASE
WHEN LEN(Id) < 2 THEN REPLACE(STR(Id,3),' ',0)
ELSE Id
END AS Id,
Val FROM Increment