January 9, 2013 at 11:28 pm
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
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
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
Thanks for the script.
May 2, 2016 at 4:18 pm
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy