:)Or in a simple query:

SELECTstring Original

,CASEWHEN ISNUMERIC(string) = 0

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

ELSE '' END-- Characters first

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

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

ASModified

FROM @TestData