The following query should also take care of values without digits:

SELECTstring Original

,CASEWHEN PATINDEX('%[0-9]%',string) > 0

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

ELSE RTRIM(string) END-- Characters first

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

+ CASEWHEN PATINDEX('%[0-9]%',string) > 0

THEN SUBSTRING(string, PATINDEX('%[0-9]%',string), 4)

ELSE '' END-- Numeric Trailer

ASModified

FROM @TestData