CODEWITHSUNDEEP
oracle-database
sdm
sdm

Reputation: 33

unique identifier for each name and surname in oracle

I have table like below with first and last name:

person1 person2
Helene Fischer Jennifer Lopez
Beyonce Jennifer Lopez
Jennifer Lopez Beyonce

as query result, I want to get unique ID for each name and surname like below:

person1 person2 uniqueId1 uniqueId2
Helene Fischer Jennifer Lopez A123 A124
Beyonce Jennifer Lopez A125 A124
Jennifer Lopez Beyonce A124 A125

how I can prepare my "select" script for result table? thanks in advance

Upvotes: 0

Views: 264

Answers (1)

Alex Poole
Alex Poole

Reputation: 191455

There are various ways to approach this. One is to hash the names, e.g.:

select person1, person2,
  standard_hash(person1, 'MD5') as unique_id1,
  standard_hash(person2, 'MD5') as unique_id2
from your_table;
PERSON1 PERSON2 UNIQUE_ID1 UNIQUE_ID2
Helene Fischer Jennifer Lopez 0x98E66FAC0FD3E55EC79F7538842067E6 0xE41C2D43B624E9237CE154512F47DE53
Beyonce Jennifer Lopez 0x3AE5DABEA04CBA4CE60FA41DFAF36EEC 0xE41C2D43B624E9237CE154512F47DE53
Jennifer Lopez Beyonce 0xE41C2D43B624E9237CE154512F47DE53 0x3AE5DABEA04CBA4CE60FA41DFAF36EEC

Or you could get all the unique names and assign a rank or GUID to each one:

select person, row_number() over (order by person), sys_guid()
from (
  select person1 as person
  from your_table
  union
  select person2
  from your_table
);

and then use that as an inline view or CTE to join to:

with ids (person, id) as (
  select person, row_number() over (order by person)
  from (
    select person1 as person
    from your_table
    union
    select person2
    from your_table
  )
)
select person1, person2,
  ids1.id as unique_id1,
  ids2.id as unique_id2
from your_table
join ids ids1 on ids1.person = person1
join ids ids2 on ids2.person = person2;
PERSON1 PERSON2 UNIQUE_ID1 UNIQUE_ID2
Helene Fischer Jennifer Lopez 2 3
Beyonce Jennifer Lopez 1 3
Jennifer Lopez Beyonce 3 1

db<>fiddle with those and some minor variations.

A better long-term prospect would be to have a separate table with the unique names and an ID, and make the values in this table have a foreign key relationship to enforce data integrity.

Upvotes: 1

Related Questions