Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLNOVICE999
Yak Posting Veteran
62 Posts |
 Posted - 2012-01-13 : 10:46:21
|
| Hi Guys,I am trying to replace random substring of numbers with random numbers from a varchar string...Ex.(915) 676-9090 ext 303What I need to do is replace the 6 numeric characters on the right with random numbers..Like above can be:(915) 676-9512 ext 245Checking if someone has done something like this...THanks,Laura |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-13 : 10:51:50
|
| didnt understand the need for this. can you elaborate?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
SQLNOVICE999
Yak Posting Veteran
62 Posts |
Posted - 2012-01-13 : 10:54:22
|
Thanks Visakh for the response. Actually I have bunch of phone number fields with US and international numbers that I need to Obfuscate and I need to keep the country code and state codes intact... the numbers have characters as well... so I was thinking to replace last 5 or 6 numeric characters. Hope I am clear. quote:
Originally posted by visakh16
didnt understand the need for this. can you elaborate?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-13 : 11:06:37
|
Could you replace them always with XXXX or **** ? If you do randomize numbers, isn't it possible that that would happen to be someone else's number, and the bad guys may call that number and get to someone within the organization in any case?Anyway, didn't mean to lecture you. Here is one way of replacing the last four digits with an almost truly random number. However, this may be overkill for your purposes.DECLARE @phone VARCHAR(32); SET @phone = '(915) 676-9090 ext 303'SELECT STUFF(@phone,CHARINDEX('-',@phone)+1,4,RIGHT('0000'+CAST(crypt_gen_random(2)%10000 AS VARCHAR(4)),4)); |
|
|
|
SQLNOVICE999
Yak Posting Veteran
62 Posts |
Posted - 2012-01-13 : 11:12:30
|
Sunita thanks for the response... this is something I could have used if I knew that last 4 characters were numbers..It can be something like (915) 898-0987 ext 78 for which I want 98778 to be replaced with random numbers.. I need to keep other characters like ext or - or () or anything else as it is and replace just the last 5 numeric characters... The idea of randomizing those digits is that individual should not be identified. XXX is something I had suggested, but they said no... so was checking. THanks for ur idea.quote:
Originally posted by sunitabeck
Could you replace them always with XXXX or **** ? If you do randomize numbers, isn't it possible that that would happen to be someone else's number, and the bad guys may call that number and get to someone within the organization in any case?Anyway, didn't mean to lecture you. Here is one way of replacing the last four digits with an almost truly random number. However, this may be overkill for your purposes.DECLARE @phone VARCHAR(32); SET @phone = '(915) 676-9090 ext 303'SELECT STUFF(@phone,CHARINDEX('-',@phone)+1,4,RIGHT('0000'+CAST(crypt_gen_random(2)%10000 AS VARCHAR(4)),4));
|
|
|
|
SQLNOVICE999
Yak Posting Veteran
62 Posts |
Posted - 2012-01-13 : 11:41:59
|
| This does it...--Select top 10000 Number = identity (int, 1,1) --into Numbers --from --sys.sysobjects--cross join sys.syscolumnsDECLARE @Input VARCHAR(25) = '(915) 676-9090 ext 303' ;;WITH Parsed AS (SELECT SUBSTRING(@Input, Number, 1) AS SString, -- breaks the string up to individual characters Number AS Row FROM dbo.Numbers WHERE Number BETWEEN 1 AND LEN(@Input)), ReplaceNums AS (SELECT TOP 6 -- Last 6 that are 0-9 Row, CAST(ABS(CHECKSUM(NEWID())) % 10 AS VARCHAR(10)) AS NewNum -- "random" new digit FROM Parsed WHERE SString LIKE '[0-9]' ORDER BY Row DESC) SELECT ( SELECT COALESCE(NewNum, SString) -- Re-concatenates the string together FROM Parsed LEFT OUTER JOIN ReplaceNums ON Parsed.Row = ReplaceNums.Row ORDER BY Parsed.Row FOR XML PATH(''), TYPE).value('.[1]', 'varchar(25)') ; |
|
|
|
|
|
|
|
|
Replace subsstring with random numbers