The addition zero is coming from the fact that it's automatic code... it figures out that the "Page1" entry is 5 characters long and uses that asthe length for all the others.

If you want to force 4 characters even though 5 or more may be present, then the following will work...

DECLARE @TestData TABLE(page char(32))

insert @testdata (page)

select 'G18' union all

select 'G2' union all

select 'D99' union all

select 'A08' union all

select '16' union all

select 'ENT' union all

select 'M183' union all

select 'PAGE1'

--===== This is the solution for the known constraints on the data as posted

DECLARE @MaxWidth TINYINT

SET @MaxWidth = 4

SELECT Original = page,

Modified = LEFT(d.page,d.LastLetter)

+ RIGHT(

REPLICATE('0',@MaxWidth)

+ SUBSTRING(d.page,d.LastLetter+1,@MaxWidth)

, @MaxWidth-d.LastLetter)

FROM (--==== Derived table "d" finds the interface between letters/digits

SELECT Page = RTRIM(page),

LastLetter = PATINDEX('%[a-z][0-9]%',RTRIM(page)+'0')

FROM @TestData

)d