The following code displays a trigger that I have created to generate an id number that always has to display as five characters. The field type is char but the id number format has to display as a character and 4 integers, for example: T0001.  The problem is that the code will work if I increment the number using T1000 it will successfully increment to T1001.  If I try incrementing from T0000 than the next number displays as T2. Any suggestions for code that would be able to populate the space after the the first character with leading zeros.  The code is below:

CREATE TRIGGER doctoridITrig ON tjohnso.tstvalid_doc FOR INSERT AS
DECLARE @maxss char(5), @maxsd int, @chrDOC char(5)   /* FOR COUNTER-EMULATION CODE */


/*
 * Assign doctor id number
 */

IF (SELECT Count(*) FROM inserted WHERE doctorid IS NULL) > 0
BEGIN
SELECT @chrDOC = (SELECT Max(doctorid) FROM tjohnso.tstvalid_doc WHERE
doctorid Like '%T%')
/*
The following code seperates the letter 'T' from the four numbers and places the numbers into a variable so that the id number can be incremented by 1
 */
IF @chrDOC IS NULL SELECT @maxsd = convert(int,(substring(@chrDOC, 2,4)))
ELSE
BEGIN
SELECT @chrDOC = SUBSTRING(@chrDOC, 2,4)
SELECT @maxsd = CONVERT(int, @chrDOC)
END
SELECT @maxsd = @maxsd + 1
SELECT @chrDOC = CONVERT(char(5), @maxsd )

UPDATE tjohnso.tstvalid_doc SET doctorid = 'T' + @chrDOC WHERE doctorid IS NULL
END



GO

Any help or ideas would be greatly appreciated.  Thank you.

Hopefully following code might help:

declare @nvalue char(4),
@id char(5),
@number int

--for example its 'T0022'
select @id = 'T0022'

select @nvalue = substring(@id,2,4)

select @number = convert(int,@nvalue)

select @number = @number + 1

select @nvalue = convert(char(4),@number)


select substring(@id,1,1) + replicate('0',4 - len(@nvalue)) + @nvalue

SELECT @maxsd = CONVERT(int, @chrDOC)

I think this is where the problem is, the datatype INT is what is converting it from 000 to 2 when you add. Try to use numeric.

Thank you LuvASP and Stan123 for your suggestions.  I finally created the following code for my counter:

CREATE TRIGGER doctoridITrig ON tjohnso.tstvalid_doc FOR INSERT AS
DECLARE @maxss char(4), @maxsd int, @chrDOC char(5), @chrDOC1 char(5)   /* FOR COUNTER-EMULATION CODE */


/*
 * Assign doctor id number
 */

IF (SELECT count(*) FROM inserted WHERE doctorid IS NULL) > 0
BEGIN



SELECT @chrDOC = (SELECT Max(doctorid) FROM tjohnso.tstvalid_doc)


IF @chrDOC IS NULL SELECT @maxsd = convert(int,(substring(@chrDOC, 2,4)))
ELSE
BEGIN
select @chrDOC1 = @chrDOC
SELECT @chrDOC = SUBSTRING(@chrDOC, 2,4)
sELECT @maxsd = CONVERT(int, @chrDOC)
END
SELECT @maxsd = @maxsd + 1
select @maxss = convert(char(4),@maxsd)

UPDATE tjohnso.tstvalid_doc SET doctorid =

case
when len(ltrim(rtrim(@maxss))) = 1 then (ltrim(rtrim(substring(@chrDOC1,1,1)))) + '000' + @maxss
when len(ltrim(rtrim(@maxss))) = 2 then (ltrim(rtrim(substring(@chrDOC1,1,1)))) + '00' + @maxss
when len(ltrim(rtrim(@maxss))) = 3 then (ltrim(rtrim(substring(@chrDOC1,1,1)))) + '0' + @maxss
else @maxss
end

WHERE doctorid IS NULL
END


Now all I have to do is get the code to change to another letter once the first 9999 nubers have been used for the current letter.