Generating 3 random seed for SSN

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Generating 3 random seed for SSN

Author

Topic

priyasunkar
Starting Member

3 Posts

Posted - 2012-01-04 : 10:55:53

Hi,

i need to generate 3 random seed for SSN.

For example: 123 45 6789 is the SSN

then i need to replace 1 with some random number and 4 with some random number and 6 with some random number.

how can i do that?

Please help me..
Thankyou.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-04 : 12:18:52

see

http://beyondrelational.com/blogs/madhivanan/archive/2007/10/10/generating-random-numbers-part-ii.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-04 : 13:15:31

Yes...BUT

Assuming you are doing Data Scrambling to protect the data, you had BETTER have an algorithm the guarantees that any scrambled SSN is not Duplicated with your "randomness"

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

priyasunkar
Starting Member

3 Posts

Posted - 2012-01-04 : 14:38:01

Thanks Visakh..:)

Thanks Brett..im doing data scrambling for the SSN...can you give an idea of algorithm how do i need to start working on it would realyy helps..

Thank you

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-01-04 : 23:58:44

[code]
select
aa.*
from
(
select
a.*,
NEW_SSN =
-- left random digit (1 to 9)
(((convert(bigint,convert(varbinary(7),newid()))%9)+1)*100000000)+
-- next two digits
(((ssn%100000000)/1000000)*1000000)+
-- next random digit (0 to 9)
((convert(bigint,convert(varbinary(7),newid()))%10)*100000)+
-- next digits
(((ssn%100000)/10000)*10000)+
-- last random digit (0 to 9)
((convert(bigint,convert(varbinary(7),newid()))%10)*1000)+
-- last 3 digits
(ssn%1000)
from
( -- test values
select ssn = 123456789 union all
select ssn = 178463458
) a
cross join
( --generate rows
select top 8 x=1 from syscolumns
) b

) aa
where
-- eliminate dupes
aa.SSN <> aa.NEW_SSN
group by
-- eliminate dupe rows
aa.SSN,
aa.NEW_SSN
order by
aa.SSN,
aa.NEW_SSN
[/code]

Results:
[code]
ssn NEW_SSN
----------- --------------------
123456789 123552789
123456789 223457789
123456789 423753789
123456789 623855789
123456789 723657789
123456789 823159789
123456789 823753789
123456789 923154789
178463458 178160458
178463458 478360458
178463458 478666458
178463458 578360458
178463458 578369458
178463458 678764458
178463458 778866458
178463458 978564458

(16 row(s) affected)

[/code]

CODO ERGO SUM

priyasunkar
Starting Member

3 Posts

Posted - 2012-01-05 : 12:12:06

It works great thank you so much Micheal...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources