Replace substring with random numbers

Question

Post reply

Replace substring with random numbers

Laura_SqlNovice

SSCrazy

Points: 2751
More actions
January 13, 2012 at 8:33 am

#249611

Hi Guys,
I am trying to replace random substring of numbers with random numbers from a varchar string...
Ex.
(915) 676-9090 ext 303
What I need to do is replace the 6 numeric characters on the right with random numbers..
Like above can be:
(915) 676-9512 ext 245
Checking if someone has done something like this...
THanks,
Laura

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Will the input value always be a formatted phone number with a 3-digit extension?

If so, you can brute-force it pretty easily:

DECLARE @Input CHAR(22) = '(915) 676-9090 ext 303', @Output CHAR(22);

SELECT @Output = STUFF(@Input, 12, 1, CAST(ABS(CHECKSUM(NEWID())) % 10 AS VARCHAR(10)));

SELECT @Output = STUFF(@Output, 13, 1, CAST(ABS(CHECKSUM(NEWID())) % 10 AS VARCHAR(10)));

SELECT @Output = STUFF(@Output, 14, 1, CAST(ABS(CHECKSUM(NEWID())) % 10 AS VARCHAR(10)));

SELECT @Output = STUFF(@Output, 20, 1, CAST(ABS(CHECKSUM(NEWID())) % 10 AS VARCHAR(10)));

SELECT @Output = STUFF(@Output, 21, 1, CAST(ABS(CHECKSUM(NEWID())) % 10 AS VARCHAR(10)));

SELECT @Output = STUFF(@Output, 22, 1, CAST(ABS(CHECKSUM(NEWID())) % 10 AS VARCHAR(10)));

SELECT @Input AS [Before], @Output AS [After];

If the string won't always be pre-formatted, this is a bit more complex, but will handle the last 6 digits in any string.

(Note: This requires the use of a Numbers table, which is a table of integer numbers in a prebuilt range. I have one that goes from 0 to 10,000.)

DECLARE @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)') ;

Does that help?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Laura_SqlNovice SSCrazy Points: 2751 More actions · Answer 2

Laura_SqlNovice

SSCrazy

Points: 2751

January 13, 2012 at 9:40 am

#1433386

GSquared thanks a lot... this worked. Awesome.

GSquared SSC Guru Points: 260824 More actions · Answer 3

You're welcome.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Laura_SqlNovice SSCrazy Points: 2751 More actions · Answer 4

Hi GSquared,

Thanks for your help on this one. I wanted to check with you onething.. should I create function or SP for this... the data will be updated in off-hours.

Thanks,

Laura