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

Advertisements

2 Comments on “Padding Number Inside a String with T-SQL”

  1. Hari says:

    Hi, could you please look into my question on social msdn. You asked me to post what the select returned. I replied already.

    • Hari says:

      The title for my thread is ‘Select statement to query multiple database on same SQL server and use results in a report’


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s