It's not a secret anymore!*
The moose likes JDBC and the fly likes scrambling data in db - oracle sql Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "scrambling data in db - oracle sql" Watch "scrambling data in db - oracle sql" New topic
Author

scrambling data in db - oracle sql

John Landon
Ranch Hand

Joined: Sep 25, 2008
Posts: 227
Hi,
I need to scramble data in db.
For NAME I use:



for ssn:


but what do I do with phones?
they stored like this:
(111) 987-8976

I want to scramble only numbers and get numbers back and do not want to touch ')', '(', '-' and spaces.

Thanks.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    5

Ummm.... What? I gotta hear the use case for this one.

BTW, why not just set the values to null and/or the empty string? Better yet, delete the columns with the data.


My Blog: Down Home Country Coding with Scott Selikoff
John Landon
Ranch Hand

Joined: Sep 25, 2008
Posts: 227
What more context you need?
I gave you examples.
If I wanted to delete the data I would do it. I need to scramble it.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    5

And how would you unscramble it?
John Landon
Ranch Hand

Joined: Sep 25, 2008
Posts: 227
Can you please answer the question if you can?
Who said I will ever need to unscramble it.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    5

What's the point of scrambling data that cannot be unscrambled? If you're throwing data away, then just throw it away! Delete the column and be done with it.
John Landon
Ranch Hand

Joined: Sep 25, 2008
Posts: 227
Scott Selikoff wrote:What's the point of scrambling data that cannot be unscrambled? If you're throwing data away, then just throw it away! Delete the column and be done with it.


Did you think about testing performance on the same size of data?

Guys please do not "answer" if you actually do not have an answer. Because others think I am all set due to number of replies.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    5

Since you're not likely to do a range query on SSN and your scramble technique will produce duplicates eventually, just set them all to the same value. Also, one doesn't usually base test data on a permutation of production data as in many data sets the unique customers information can still be determined.

Every company I've worked with required test data to be generated/created from scratch, with a strict wall between production and test data. In fact, in many companies only a handful of developers have access to production data, if at all. I'm trying to point out that there's a fundamental flaw in what you're asking, namely that there are better ways to create test data than using real data.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

What about this:

By the way, it might well be faster to create as select a new table (with the /*+append */ hint) than to update all rows in your table.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: scrambling data in db - oracle sql
 
Similar Threads
getblob from db, then convert to byte and write to bitmap/jpeg
JSP and Javascript problem
CMR Relations one-to-many
Data synchronization bet practice?
Collections - Search by value