Interesting, definitely some syntax in there I have to learn..

Jeff, your "auto magic" one came the closest but I got some extra zeros I haven't quite figured yet -

Original Modified

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

A14 A00014

B2 B0002

OBT 0000OBT

YH3 YH0003

XX 0000XX

So - too many zeroes, the whole string must be 4. And if there was no digit found it puts the zeros at the beginning of the string rather than after the letters.

Your query accomodating for blanks didn't give me any modification, similarly the earlier non-auto magic query didn't produce a modification.

And the other query offered, the simple one -

SELECTString Original

,CASEWHEN ISNUMERIC(Page) = 0

THEN LEFT(Page, PATINDEX('%[0-9]%',String)-1)

ELSE '' END-- Characters first

+ REPLICATE('0', 4-LEN(Page))-- Stuff with zeros

+ SUBSTRING(String, PATINDEX('%[0-9]%',String), 4)-- Add numeric remainder

ASModified

FROM @TestData

This one gives me some results for short page lengths but none of the more complex ones are returned and I got an error "invalid length parameter passed to the substring function."

The query would only be evaluating records where the field has content (so no worries about blanks) and where the content is less than 4 characters. If it has 4 or more it will be ignored in the update.