0

I am in the situation that inside the database various double values are stored as a string.
(this can not be changed due to some other reasons!)

The numbers can have a different amount of numbers after & before the decimal separator.
The decimal separator of the stored values is a .
The default decimal separator of the database might possibly change in the future.

Examples:

1.1  
111.1  
1.111  
11.11  
1.1111

I now need to select those as numbers to be able to compare for bigger or smaller values, etc.

Therefore I tried to convert the strings to numbers. I found a hint at this answer: click.

Unfortunately using this as a test:

SELECT TO_NUMBER('10.123', TRANSLATE('10.123', ' 1,234.567890', TO_CHAR(9999.9, '9G999D9') || '99999'))
FROM DUAL;

Somehow converts the number to 10123, completely removing the decimal separation, so this query gives no result (just for verification):

SELECT * FROM(SELECT TO_NUMBER('10.123', TRANSLATE('10.123', ' 1,234.567890', TO_CHAR(9999.9, '9G999D9') || '99999')) AS NUM
FROM DUAL) WHERE NUM < 11;

So I stepped through the single parts to see if I can find an error:

SELECT TO_CHAR(9999.9, '9G999D9') FROM DUAL; -- 9.999,9

SELECT TO_CHAR(9999.9, '9G999D9') || '99999' FROM DUAL; -- 9.999,999999

SELECT TRANSLATE('10.123', ' 1,234.567890', ' 9.999,999999')
FROM DUAL; -- 99,999

SELECT TRANSLATE('10.123', ' 1,234.567890', TO_CHAR(9999.9, '9G999D9') || '99999')
FROM DUAL; -- 99,999

As you can see I get a . as group separator and a , as decimal separator for the database.

I do not understand why it does not correctly convert the number.
Thank you already for any help!

JDC
  • 4,247
  • 5
  • 31
  • 74

2 Answers2

1

Try using this version of to_number

TO_NUMBER( string1 [, format_mask] [, nls_language])

For example:

SELECT to_number('1.1111','9G990D00000', 'NLS_NUMERIC_CHARACTERS = ''.,''') FROM DUAL

PKey
  • 3,715
  • 1
  • 14
  • 39
0

You can try this,

alter session set NLS_NUMERIC_CHARACTERS = '.,';

WITH INPUT_TEST AS (
    SELECT '.' decimal_operator, '1.1' num_in_char from dual
    UNION ALL
    SELECT '.' decimal_operator, '111.1  ' from dual
    UNION ALL
    SELECT '.' decimal_operator, '1.111 ' from dual
    UNION ALL
    SELECT '.' decimal_operator, '11.11  ' from dual
    UNION ALL
    SELECT '.' decimal_operator, '1.1111' from dual)
SELECT TO_NUMBER(REPLACE(num_in_char, '.', decimal_separator)) to_num
  FROM input_test a, (select SUBSTR(value, 1, 1) decimal_separator 
                        from nls_session_parameters 
                       where parameter = 'NLS_NUMERIC_CHARACTERS') b;

    TO_NUM
----------
       1.1
     111.1
     1.111
     11.11
    1.1111

alter session set NLS_NUMERIC_CHARACTERS = ',.';

Run the select statement above again.

    TO_NUM
----------
       1,1
     111,1
     1,111
     11,11
    1,1111
eifla001
  • 1,137
  • 8
  • 8