convert to number (string) by keeping the same number

Question

I have a string like € 1.580 (1580 €). By doing the following statement the number become:

select to_number(SUBSTR(col1,2)) from TAB1 where riga = 2;
Number Result: 1.58

I want to keep the 0 because the real number is 1580 and I want to display 1.580. How can I do that?

Thank you.

Answer 1

A couple of options using the NLS_NUMERIC_CHARACTERS and NLS_CURRENCY options with TO_NUMBER:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE tab1 (
  col1 VARCHAR2( 200 )
);

INSERT INTO tab1 VALUES ( '€ 1.580' );

Query 1:

SELECT TO_NUMBER(
        SUBSTR( col1, 2 ),
         '999G999',
         'NLS_NUMERIC_CHARACTERS='',.'''
       ) AS value1,
       TO_NUMBER(
         REPLACE( col1, ' ' ),
         'L999G999',
         'NLS_CURRENCY=''€'' NLS_NUMERIC_CHARACTERS='',.'''
       ) AS value2
FROM   tab1

Results:

| VALUE1 | VALUE2 |
|--------|--------|
|   1580 |   1580 |

If you want to re-format it as a string then, just use TO_CHAR again:

Query 2:

SELECT TO_CHAR(
         TO_NUMBER(
           REPLACE( col1, ' ' ),
           'L999G999',
           'NLS_CURRENCY=''€'' NLS_NUMERIC_CHARACTERS='',.'''
         ),
         'FM999G999',
         'NLS_NUMERIC_CHARACTERS='',.'''
       )AS value2
FROM   tab1

Results:

| VALUE2 |
|--------|
|  1.580 |

But you ought to be storing the value as a number and whenever you want to format it as a currency then just use TO_CHAR( value, 'L999G999' ) with the appropriate NLS options and do NOT store it as a formatted string.