# Find and replace a number with in a string • ### Question

• Hi Experts,

It would be great if you can share the logic to replace each number in a string.

Ex 1: String - 5AB89C should be converted as 0000000005AB0000000089C

Ex 2: String GH1HJ should be converted as GH0000000001HJ

Ex 3: String N99K7H45 should be B0000000099K0000000007H0000000045

Each number should be complimented with 10 leading zeros including the number. In Ex 1, number 5 is complemented with 9 leading zeros making 10 digits, same way 89 is complimented with 8 leading zeros making total of 10 digits. Alphabets and any special characters should be untouched.

Thanks,

Naveen J V

Naveen J V

Tuesday, April 9, 2019 10:51 AM

• Here's a function that should do the trick

```CREATE FUNCTION dbo.sfn_AddZeros
(
@Input varchar(100)
)
RETURNS varchar(500)
AS
BEGIN
DECLARE @Result varchar(500) = '',
@Position int = 1,
@Length int,
@CurrentChar char(1),
@NextChar char(1),
@Buffer varchar(100) = '';

SET @Length = LEN(@Input);

WHILE (@Position <= @Length)
BEGIN

SET @CurrentChar = SUBSTRING(@Input, @Position, 1);

IF (ISNUMERIC(@CurrentChar) = 1)
BEGIN  	                         SET @Buffer = @Buffer + @CurrentChar;
-- End of string
IF (@Position = @Length)
BEGIN
-- Format to 10 digits
SET @Result = @Result + RIGHT('0000000000' + @Buffer, 10);
END
ELSE
BEGIN
-- We need to get consecutive numbers
SET @NextChar = SUBSTRING(@Input, @Position + 1, 1);
IF (ISNUMERIC(@NextChar) = 0)
BEGIN
SET @Result = @Result + RIGHT('0000000000' + @Buffer, 10);
END
END
END
ELSE
BEGIN
-- this is not a number, add it to result
SET @Result = @Result + @CurrentChar;
-- clear buffer
SET @Buffer = '';
END

SET @Position = @Position + 1;
END

-- Return the result of the function
RETURN @Result;

END
GO
```

Tests:

```/*
Ex 1: String - 5AB89C should be converted as 0000000005AB0000000089C
Ex 2: String GH1HJ should be converted as GH0000000001HJ
Ex 3: String N99K7H45 should be B0000000099K0000000007H0000000045
*/

• Edited by Tuesday, April 9, 2019 3:07 PM Optimized
• Proposed as answer by Tuesday, April 9, 2019 3:17 PM
• Marked as answer by Wednesday, April 10, 2019 12:50 PM
Tuesday, April 9, 2019 2:39 PM
• It is really slow.

```

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)

)

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)')

drop table test```

• Marked as answer by Wednesday, April 10, 2019 12:50 PM
Tuesday, April 9, 2019 4:48 PM

### All replies

• hmm, it's either a normalization issue, cause your having non-atomic data or like a formatting issue, then it is better done in the front-end.

From the performance view point: When it must be done on the server-side, use a SQL CLR and a regex. String splitting in T-SQL is pretty expensive (using PATINDEX to find the first [A-Z], then using SUBSTRING to extract the parts).

• Proposed as answer by Tuesday, April 9, 2019 3:17 PM
Tuesday, April 9, 2019 11:13 AM
• Here's a function that should do the trick

```CREATE FUNCTION dbo.sfn_AddZeros
(
@Input varchar(100)
)
RETURNS varchar(500)
AS
BEGIN
DECLARE @Result varchar(500) = '',
@Position int = 1,
@Length int,
@CurrentChar char(1),
@NextChar char(1),
@Buffer varchar(100) = '';

SET @Length = LEN(@Input);

WHILE (@Position <= @Length)
BEGIN

SET @CurrentChar = SUBSTRING(@Input, @Position, 1);

IF (ISNUMERIC(@CurrentChar) = 1)
BEGIN  	                         SET @Buffer = @Buffer + @CurrentChar;
-- End of string
IF (@Position = @Length)
BEGIN
-- Format to 10 digits
SET @Result = @Result + RIGHT('0000000000' + @Buffer, 10);
END
ELSE
BEGIN
-- We need to get consecutive numbers
SET @NextChar = SUBSTRING(@Input, @Position + 1, 1);
IF (ISNUMERIC(@NextChar) = 0)
BEGIN
SET @Result = @Result + RIGHT('0000000000' + @Buffer, 10);
END
END
END
ELSE
BEGIN
-- this is not a number, add it to result
SET @Result = @Result + @CurrentChar;
-- clear buffer
SET @Buffer = '';
END

SET @Position = @Position + 1;
END

-- Return the result of the function
RETURN @Result;

END
GO
```

Tests:

```/*
Ex 1: String - 5AB89C should be converted as 0000000005AB0000000089C
Ex 2: String GH1HJ should be converted as GH0000000001HJ
Ex 3: String N99K7H45 should be B0000000099K0000000007H0000000045
*/

• Edited by Tuesday, April 9, 2019 3:07 PM Optimized
• Proposed as answer by Tuesday, April 9, 2019 3:17 PM
• Marked as answer by Wednesday, April 10, 2019 12:50 PM
Tuesday, April 9, 2019 2:39 PM
• It is really slow.

```

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)

)

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)')

drop table test```

• Marked as answer by Wednesday, April 10, 2019 12:50 PM
Tuesday, April 9, 2019 4:48 PM
• Thanks you Bill

Naveen J V

Wednesday, April 10, 2019 12:50 PM
• Thank you Jingyang Li

Naveen J V

Wednesday, April 10, 2019 12:51 PM