How to replace id-value with new fictive id in oracle

Posted on 2012-04-10
Last Modified: 2012-04-15

I have a table with, let´s say, 4 columns, person, colA, col B and colC.

The first column contains personal number (ssn) so when I export data to outside persons I dont want to send also the actual value in column.  Instead I want to replace, in this case "760320" with "1" and 760914 with "2".  Please note that 760320 and other personal numbers can occure 1, 2 or several times.

person      col A          col B            col C
760320      x          y                       z
760320      x          y                       z
760914      x          y                       u

The result should be

person  col A colB       col C
1             x      y           z
1             x      y           z
2             x      y            u

Thanks in advance
Question by:marcgu
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 74

Accepted Solution

sdstuber earned 300 total points
ID: 37829712
select dense_rank() over(order by person) person, cola,colb,colc from yourtable
order by person
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37829717
>>when I export data to outside

Do you mean Oracle Export?

If you are running 11g and plan on using datapump, check out:  
Data Pump Data Remapping (Obfuscation)

If you mean some other type of expert, please clarify.

Author Comment

ID: 37829759

Thanks for really fast possible solutions.

sdstuber: I will try your solution at work tomorrow.

slightwv: I wrote export, but the only import thing is really to get the result from the select-query in SQL developer . From there I can choose to copy the rows from the select or right click  to export. Sorry if this caused false focus.
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37829770
Are you only wanting a single table or will you be exporting additional related tables and need to keep the obfuscated values equal across the exports?

Author Closing Comment

ID: 37848223
Thanks for perfect solution.

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question