将货币符号转换为货币代码:SQL

Question

我有两个独立的数据源，它们有一个列货币

Currency作为USD存在另一个是$作为数据存在。

有没有办法将符号转换为美元？

如果我们有multiple currencies, for eg: euro, CAD呢

Answer 1

创建查找表：

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;

然后，您可以将数据源加入查找表，并选择货币代码值而不是符号：

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

Answer 2

是，使用CASE或DECODE，例如

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>