Forum Stats

  • 3,784,147 Users
  • 2,254,897 Discussions
  • 7,880,711 Comments

Discussions

reading number string with command and decimal

aliyesami-JavaNet
aliyesami-JavaNet Member Posts: 1
edited Jun 13, 2019 9:56AM in SQL & PL/SQL

how can i read a text string  like "31,432.99"  as number 31432.99 ?

i tried using <span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">NLS_NUMERIC_CHARACTERS  but it reads the strings with comma properly but does not read if i put the</span>

decimal point.

thanks

<span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">alter</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> session  </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">set</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> NLS_NUMERIC_CHARACTERS</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'.,'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">;</span>

mNemGaz in Oz

Answers

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,674 Red Diamond
    edited Jun 13, 2019 1:19AM

    Explicit formatting with type conversions, is more robust IMO:

    SQL> select '31,432.99' as "STRING", to_number('31,432.99','999,999,999,999.99') as "NUMBER" from dual;STRING        NUMBER--------- ----------31,432.99   31432.991 row selected.
    Gaz in Oz
  • mNem
    mNem Member Posts: 1,380 Gold Trophy
    edited Jun 13, 2019 1:34AM

    with t ( y ) as
    (
    select '325,999.0854' from dual union all
    select '0.45' from dual union all
    select '450' from dual union all
    select '456,789' from dual
    )
    select to_number(y, '9G999G999D999999999') n from t;

             N
    ----------
    325999.085
           .45
           450
        456789

    ----------------------

    EDIT:

    on the other hand, if you have a mixture of strings that do not conform to the format model then it will complain ...

    with t ( y ) as
    (
    select '456,789' from dual union all
    select '456789' from dual
    )
    select to_number(y, '9G999G999D999999999') n from t;

    ORA-01722: invalid number
    01722. 00000 -  "invalid number"

  • RogerT
    RogerT Member Posts: 1,855 Gold Trophy
    edited Jun 13, 2019 2:28AM

    To go further...

    with t ( y ) as

    (

    select '456,789' from dual union all

    select '456789' from dual union all

    select '456.789' from dual

    )

    select coalesce(to_number(y default null on conversion error)

                   ,to_number(y default null on conversion error, '9G999G999D999999999',q'{nls_numeric_characters=',.'}')

                   ,to_number(y default null on conversion error, '9G999G999D999999999',q'{nls_numeric_characters='.''}')

                   ) n

      from t;

    trying to use different conversions on a string.

    hth

    mNem
  • Stew Ashton
    Stew Ashton Member Posts: 2,864 Bronze Crown
    edited Jun 13, 2019 7:29AM

    Hi,

    In my version (18.3) I see problems with the group separator, not the decimal. In that case, I can make the problem go away by removing the group separator.

    UPDATE: fixed copy/paste error pointed out by padders.

    alter session set nls_numeric_characters=".,";select to_number(replace('1,345,678.1', ',')) num from dual;       NUM---------- 1345678.1

    This may convert some 'invalid' number strings to valid numbers, for example '9,9,9,9,,9.9'

    If you want to make sure the strings contain valid numbers:

    alter session set nls_numeric_characters=".,";with ints(i) as (  select '1' from dual union all  select '12' from dual union all  select '123' from dual union all  select '1234' from dual union all  select '1,345' from dual union all  select '12,456' from dual union all  select '123,567' from dual union all  select '1,345,678' from dual), fractions(f) as (  select '1' from dual union all  select '12' from dual union all  select '123' from dual union all  select '1234' from dual), strings(s) as (  select i num from ints  union all  select i||'.'||f from ints, fractions)select s,   case instr(s,',') when 0    then to_number(s, 'fm99999999999D9999', q'<nls_numeric_characters='.,'>')    else to_number(s, 'fm999G999G999D9999', q'<nls_numeric_characters='.,'>')  end nfrom stringsorder by 2,1;S                       N-------------- ----------1                       11.1                   1.11.12                 1.121.123               1.1231.1234             1.123412                     1212.1                 12.112.12               12.1212.123             12.12312.1234           12.1234123                   123123.1               123.1123.12             123.12123.123           123.123123.1234         123.12341234                 12341234.1             1234.11234.12           1234.121234.123         1234.1231234.1234       1234.12341,345                13451,345.1            1345.11,345.12          1345.121,345.123        1345.1231,345.1234      1345.123412,456              1245612,456.1          12456.112,456.12        12456.1212,456.123      12456.12312,456.1234    12456.1234123,567            123567123,567.1        123567.1123,567.12      123567.12123,567.123    123567.123123,567.1234   123567.1231,345,678         13456781,345,678.1     1345678.11,345,678.12   1345678.121,345,678.123  1345678.121,345,678.1234 1345678.12

    Best regards,

    Stew Ashton

  • padders
    padders Member Posts: 1,062 Silver Trophy
    edited Jun 13, 2019 4:30AM

    I don't think you posted your example quite right Stew, you have semi-colon after first non-factored query.

  • Stew Ashton
    Stew Ashton Member Posts: 2,864 Bronze Crown
    edited Jun 13, 2019 7:30AM
    padders wrote:I don't think you posted your example quite right Stew, you have semi-colon after first non-factored query.

    Thanks for catching that, padders! I have corrected the error.

    Best regards,

    Stew

  • mathguy
    mathguy Member Posts: 10,229 Blue Diamond
    edited Jun 13, 2019 9:56AM
    aliyesami-JavaNet wrote:how can i read a text string like "31,432.99" as number 31432.99 ? i tried using <span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">NLS_NUMERIC_CHARACTERS but <strong>it reads the strings with comma properly</strong> but does not read if i put the</span>decimal point.thanks<span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">alter</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> session </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">set</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> NLS_NUMERIC_CHARACTERS</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'.,'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">;</span>

    It reads the string with comma properly? How were you able to do that?

    The only way that '31,432' would be read properly, even with the nls numeric characters set correctly, is STILL only by giving a specific numeric format model when you use TO_NUMBER. Otherwise this would happen:

    alter session  set NLS_NUMERIC_CHARACTERS= '.,';select to_number('31,432') from dual;select to_number('31,432') from dual;ORA-01722: invalid number01722. 00000 -  "invalid number"*Cause:    The specified number was invalid.*Action:  Specify a valid number.

    So - HOW did you try to "read a text string as number"? The exact code you used, please?

    All the information you received (which essentially says that if the input string uses thousands delimiters, you must use the numeric format model in the call to TO_NUMBER, it is not enough to set NLS_NUMERIC_CHARACTERS) is certainly correct, but it doesn't explain how you were able to read '31,432' correctly in the first place.