Yes I did try that one, and it didn't appear to make a modification, but I ran it again, here's a snippet of the results:

Original Modified

-----------------------------------

B10 B0

B101 B01

B11 B1

B12 B2

Here's a test sample of what my data looks like, this should get you the same results as I see.

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'

DECLARE @MaxWidth TINYINT

SET @MaxWidth = (SELECT MAX(LEN(Page)) FROM Arch1)

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,

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

FROM @testdata

)d

group by page, d.lastletter

order by page