none
Find and replace a number with in a string RRS feed

  • 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

Answers

  • 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
    */
    print dbo.sfn_AddZeros('5AB89C')
    print dbo.sfn_AddZeros('GH1HJ')
    print dbo.sfn_AddZeros('N99K7H45')


    • Edited by Wild.Bill Tuesday, April 9, 2019 3:07 PM Optimized
    • Proposed as answer by Lokesh Vij Tuesday, April 9, 2019 3:17 PM
    • Marked as answer by Naveen JV 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)
    
    )
     
     
     
    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

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

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 Lokesh Vij 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
    */
    print dbo.sfn_AddZeros('5AB89C')
    print dbo.sfn_AddZeros('GH1HJ')
    print dbo.sfn_AddZeros('N99K7H45')


    • Edited by Wild.Bill Tuesday, April 9, 2019 3:07 PM Optimized
    • Proposed as answer by Lokesh Vij Tuesday, April 9, 2019 3:17 PM
    • Marked as answer by Naveen JV 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)
    
    )
     
     
     
    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

    • Marked as answer by Naveen JV Wednesday, April 10, 2019 12:50 PM
    Tuesday, April 9, 2019 4:48 PM
    Moderator
  • 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