Search code examples
sqloracle-databasemasking

Create an alternative ID in query Oracle SQL

I am trying to add a column in a SQL query to use as an alternative ID. The data has this format:

UserID | Value
---------------
1      | 23
2      | 10
1      | 45

I'd like to create another column that is another number but respects the ID uniqueness. Like so

MaskedID | Value
---------------
9      | 23
8      | 10
9      | 45

I've tried using a subquery which creates a table with a random number, but the random number is not staying the same for the first ID:

Select b.Masked, a.value
from table a
left join 
(select distinct(UserID), dbms_random.value(1,100000) as Masked) b on a.UserID=b.UserID

But that results in:

MaskedID | Value
---------------
7      | 23
8      | 10
9      | 45

The # of userIDs may change with time so it's not something that should be predefined. Would a CTE keep the random numbers from being regenerated in the final table?

Solution

  • Try a hash function. It will alway return the same value for the same input, but can't be reverse engineered to the original value. The oldest and simplest is ORA_HASH:

    select ORA_HASH(USERID) Masked, a.value
from table a

    This produces a number, but with enough values you could get a collision. Even better is the newer STANDARD_HASH function which is far less likely to give a collision, but which produces an alphanumeric (hexadecimal) output:

    select STANDARD_HASH(USERID) Masked, a.value
from table a