Padding Number Inside a String with T-SQL

  

create table test (id int,col varchar(30) )

insert into test (id,col) values(1,'5AB89C'),(2,'GH1HJ'),(3,'N99K7H45')

;with  Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

,mycte as (
select *, case when PATINDEX('%[^0-9]%',mySplit)>0 then 1 else 0 end grp1,
Sum(case when PATINDEX('%[^0-9]%',mySplit)>0 then 1 else 0 end) Over(Partition by d.col Order by n) grp2
from Nums Cross apply (Select SUBSTRING(col,n,1 ), col from test) as d(mySplit, col)

)

Select   paddedCol  from test t 
Cross Apply (
 select (
 select newCol + ''
 from ( select n
,Case when grp1=0 and row_number()Over(partition by col,grp1,grp2 Order by n ) =1 
then right('0000000000'+mySplit,10) else mySplit end newCol
from mycte m
 WHERE m.col=t.col and m.mySplit<>''
 ) d
 Order by n
 FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
 ) p (paddedCol)  

drop table test

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d6fde44d-b3d0-48e6-be17-98a8ecf462cf/find-and-replace-a-number-with-in-a-string?forum=transactsql

---