You'll have to run 8 separate searches, or columns
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
--Jeff Moden
Change is inevitable... Change for the better is not.