CODEWITHSUNDEEP
databaseplsql
javafan
javafan

Reputation: 1539

Data migration from one table to another

I want to do data migration from one table to another table. Source table has country names in free floating text and destination table need to have country code.

For example source table has country names with incorrect spelling.

e.g. United Stts of Americ --> USA
Franc --> FRA

Can we have some key value pair in SQL where we can store these incorrect country names to 3 character country code?

Upvotes: 0

Views: 127

Answers (1)

Littlefoot
Littlefoot

Reputation: 142788

PL/SQL tag reads as "Oracle". If that's so, see if something like this helps.

Sample tables:

SQL> select * from state;

ABB NAME
--- ------------------------
FRA France
USA United States of America
GER Germany

SQL> select * from garbage;

NAME
----------------------
United Stts of Americ
Unted states of Amerca
Frnce
France
Gremany

SQL>

Query:

SQL> select g.name, s.name, s.abbr
  2  from state s join garbage g
  3  on utl_match.jaro_winkler_similarity(g.name, s.name) > 90;

NAME                   NAME                     ABB
---------------------- ------------------------ ---
Frnce                  France                   FRA
France                 France                   FRA
United Stts of Americ  United States of America USA
Unted states of Amerca United States of America USA
Gremany                Germany                  GER

SQL>

Tweak similarity value, if necessary. I used 90, see how it behaves for your data.

Upvotes: 2

Related Questions