Converting Currency Symbol to Currency Code: SQL-CodePudding

I have two separate data sources which has a column currency,

One has Currency present as USD Another One has $ present as data.

Is there a way to convert the symbol to USD ?

Also what if we have multiple currencies, for eg: euro, CAD

CodePudding user response：

Create a lookup table:

CREATE TABLE currencies (
  country_code  VARCHAR2(3),
  currency_code VARCHAR2(3), 
  name          VARCHAR2(50),
  symbol        VARCHAR2(5)
);

INSERT INTO currencies (country_code, currency_code, name, symbol)
SELECT 'USA', 'USD', 'US Dollar', '$' FROM DUAL UNION ALL
SELECT 'EU',  'EUR', 'Euro',      '€' FROM DUAL UNION ALL
SELECT 'GBR', 'GBP', 'GB Pound',  '£' FROM DUAL UNION ALL
SELECT 'JPN', 'JPY', 'Yen',       '¥' FROM DUAL;

Then you can join your data source to the lookup table and select the currency code value rather than the symbol:

SELECT d.col1,
       d.col2,
       d.col3,
       c.currency_code
FROM   data_source1 d
       LEFT OUTER JOIN currencies c
       ON (d.currency = c.symbol);

CodePudding user response：

Yes, using CASE or DECODE, e.g.

SQL> with test (currency) as
  2    (select 'USD' from dual union all
  3     select '$'   from dual
  4    )
  5  select currency,
  6         --
  7         case when currency = '$' then 'USD'
  8              else currency
  9         end new_currency_1,
 10         --
 11         decode(currency, '$', 'USD', currency) new_currency_2
 12  from test;

CURRENCY             NEW_CURRENCY_1       NEW_CURRENCY_2
-------------------- -------------------- --------------------
USD                  USD                  USD
$                    USD                  USD

SQL>