By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
declare @str1 nvarchar(max) = 'WAC01001';
declare @str2 nvarchar(max) = 'WAC01012';
with
cte as (
select
n,
cast(substring(@str1, n, len(@str1)) as int) num,
cast(substring(@str2, n, len(@str2)) as int) end_num,
left(@str1, n - 1) prefix
from (select patindex('%[0-9]%', @str1) n) x
union all
select
n,
num + 1,
end_num,
prefix
from cte
where num < end_num
)
select concat(
prefix,
replicate('0', len(@str1) - n - len(num) + 1),
num
) res
from cte
| res |
|---|
| WAC01001 |
| WAC01002 |
| WAC01003 |
| WAC01004 |
| WAC01005 |
| WAC01006 |
| WAC01007 |
| WAC01008 |
| WAC01009 |
| WAC01010 |
| WAC01011 |
| WAC01012 |