Post reply

Script to replace 5 consecutive digits with special character

  • January 9, 2013 at 11:28 pm

    #269093

    Comments posted to this topic are about the item Script to replace 5 consecutive digits with special character

  • January 10, 2013 at 1:03 am

    #1575622

    Your script replace 5 consecutive digits with 2 stars.

    Here an optimized version.

    declare @substr varchar(50)--substring of input string in while loop

    declare @index int--index of number in the substring

    set @substr = '1234567890abc89900123456abcde'

    while 1=1

    begin

    select @index = PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%',@substr)

    if(@index < 1)

    break

    SELECT @substr = STUFF ( @substr, @index, 5 ,'**' )

    end

    print @substr

  • January 11, 2013 at 10:12 am

    #1576417

    I love how you shortened this script Carlo. I took your script and added a few more variables to make it more flexible. Simply update the number being passed to @len to change your pattern and your replacement. I also felt if you are going to replace 5 chars you should substitute 5 chars. However I can see scenario's why you would want that to be the same across the board.

    DECLARE @substr varchar(MAX)--substring of input string in while loop

    DECLARE @index INT--index of number in the substring

    DECLARE @len INT--number of numbers being searched in the substring

    DECLARE @patindex VARCHAR(256)--patern being searched in the substring

    SET @len = 5

    SET @patindex = '%' + REPLICATE('[0-9]',@len) + '%'

    set @substr = '1234567890abc89900123456abcde'

    while 1=1

    begin

    select @index = PATINDEX(@patindex,@substr)

    if(@index < 1)

    break

    SELECT @substr = STUFF ( @substr, @index, @len ,REPLICATE('*', @len))

    end

    print @substr

  • May 2, 2016 at 4:17 pm

    #1876111

    Thanks for the script.

  • May 2, 2016 at 4:18 pm

    #1876112

    Carlo Romagnano (1/10/2013)

    Your script replace 5 consecutive digits with 2 stars.

    Here an optimized version.

    declare @substr varchar(50)--substring of input string in while loop

    declare @index int--index of number in the substring

    set @substr = '1234567890abc89900123456abcde'

    while 1=1

    begin

    select @index = PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%',@substr)

    if(@index < 1)

    break

    SELECT @substr = STUFF ( @substr, @index, 5 ,'**' )

    end

    print @substr

    Thanks for the update.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply