Or... we can cheat like hell 😛

--===== This just creates demonstrable test data and is NOT part of the solution

SET NOCOUNT ON

DECLARE @TestData TABLE(String VARCHAR(10))

INSERT INTO @TestData (String)

SELECT 'A012' UNION ALL

SELECT 'A12' UNION ALL

SELECT '12' UNION ALL

SELECT '1' UNION ALL

SELECT 'AB12' UNION ALL

SELECT 'AB2'

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

SELECT Original = String,

Modified = CASE

WHEN d.LastLetter = 0 THEN RIGHT('0000'+d.String,4)

WHEN d.LastLetter = 1 THEN LEFT(d.String,d.LastLetter)

+ RIGHT('0000'+SUBSTRING(d.String,d.FirstDigit,4),3)

WHEN d.LastLetter = 2 THEN LEFT(d.String,d.LastLetter)

+ RIGHT('0000'+SUBSTRING(d.String,d.FirstDigit,4),2)

END

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

SELECT String,

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

FirstDigit = PATINDEX('%[0-9]%',String)

FROM @TestData

)d

RESULTS:

=======

Original Modified

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

A012 A012

A12 A012

12 0012

1 0001

AB12 AB12

AB2 AB02