Get rid of currency symbols

SQL-Fighter

Hi guys,

I wonder how I can get rid of currency symbols in the exasol database (except using the function TRIM).

Moritz_0-1592914481018.png

As you can see, it works fine for display purposes.
Apparently, as soon as I perfom calculations with the referring column, the following error message occurs:

Moritz_1-1592914619155.png
Do you guys know a quick and easy solution for this?
It would be much appreciated!

Best 

Moritz 

1 ACCEPTED SOLUTION

Xpert

Hi again 😀
according to the header in your screenshot thats a CHAR / VARCHAR....not what I´d pick for something like an INCOME....
You might use REPLACE(DEBT_NET_INCOME,'€','') and trust in an implicit CAST or do the CAST on top...
Or, even better, model _INCOME-type columns as numeric 😉

View solution in original post

4 REPLIES 4

Xpert

Hi again 😀
according to the header in your screenshot thats a CHAR / VARCHAR....not what I´d pick for something like an INCOME....
You might use REPLACE(DEBT_NET_INCOME,'€','') and trust in an implicit CAST or do the CAST on top...
Or, even better, model _INCOME-type columns as numeric 😉

View solution in original post

SQL-Fighter

Thanks again! 🙂
I converted them to NUMERIC in the first place using an ETL-Tool.
This resulted in NULL entries.
Finally, the follwing statement works:
TO_NUMBER(REPLACE(REPLACE(REPLACE(DEBIT_NET_INCOME,'€',''), '.', ''), ',', '.')) AS DEBIT_NET_INCOME
Best
Moritz

SQL-Fighter

TO_NUMBER offers some more functionality to make it a bit more comfortable regarding the comma/dot:

WITH q(income) AS (
SELECT '3.600,00 €'
UNION ALL
SELECT '-9.500,00 €'
)
SELECT to_number(REPLACE(income, '€', ''), '999G999D99') AS income_numeric
FROM q
;

But this only works if you have the correct NLS-Parameters:

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.';

Additionally, all data must have the correct format. Given that this is a varchar column for a money value, this approach is probably doomed. 🙈 

Anyway, just wanted to share, that there is a different possibility.

https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/to_number.htm

https://docs.exasol.com/sql_references/formatmodels.htm#NumericFormat

Xpert

ah, yeah, sorry, missed the other alphanumeric stuff - sorry about that 😆