CODEWITHSUNDEEP
oracle-databaseplsql
bart
bart

Reputation: 7787

How to efficiently convert text to number in Oracle PL/SQL with non-default NLS_NUMERIC_CHARACTERS?

I'm trying to find an efficient, generic way to convert from string to a number in PL/SQL, where the local setting for NLS_NUMERIC_CHARACTERS settings is inpredictable -- and preferable I won't touch it. The input format is the programming standard "123.456789", but with an unknown number of digits on each side of the decimal point.

select to_number('123.456789') from dual;
  -- only works if nls_numeric_characters is '.,'

select to_number('123.456789', '99999.9999999999') from dual;
  -- only works if the number of digits in the format is large enough
  -- but I don't want to guess...

to_number accepts a 3rd parameter but in that case you to specify a second parameter too, and there is no format spec for "default"...

select to_number('123.456789', null, 'nls_numeric_characters=''.,''') from dual;
  -- returns null

select to_number('123.456789', '99999D9999999999', 'nls_numeric_characters=''.,''') from dual;
  -- "works" with the same caveat as (2), so it's rather pointless...

There is another way using PL/SQL:

CREATE OR REPLACE
FUNCTION STRING2NUMBER (p_string varchar2) RETURN NUMBER
IS
  v_decimal char;
BEGIN
  SELECT substr(VALUE, 1, 1)
  INTO v_decimal
  FROM NLS_SESSION_PARAMETERS
  WHERE PARAMETER = 'NLS_NUMERIC_CHARACTERS';
  return to_number(replace(p_string, '.', v_decimal));
END;
/

select string2number('123.456789') from dual;

which does exactly what I want, but it doesn't seem efficient if you do it many, many times in a query. You cannot cache the value of v_decimal (fetch once and store in a package variable) because it doesn't know if you change your session value for NLS_NUMERIC_CHARACTERS, and then it would break, again.

Am I overlooking something? Or am I worrying too much, and Oracle does this a lot more efficient then I'd give it credit for?

Upvotes: 12

Views: 74893

Answers (5)

Eugene Lycenok
Eugene Lycenok

Reputation: 682

Is it realistic that the number of digits is unlimited? If we assume it is then isn't it a good reason to look into the requirements more carefully?

If we have that fantastic situation when the initial string is super long, then the following does the trick:

select 
  to_number(
    '11111111.2222'
  , 'FM' || lpad('9', 32, '9') || 'D' || lpad('9', 30, '9')
  , 'NLS_NUMERIC_CHARACTERS=''.,'''
  ) 
from 
  dual

Upvotes: 0

difr
difr

Reputation: 21

select to_number(replace(:X,'.',to_char(0,'fmd'))) from dual;

btw

select to_number(replace('1.2345e-6','.',to_char(0,'fmd'))) from dual;

and if you want more strict

select to_number(translate(:X,to_char(0,'fmd')||'.','.'||to_char(0,'fmd'))) from dual;

Upvotes: 0

hvb
hvb

Reputation: 2668

Sorry, I noticed later that your question was for the other way round. Nevertheless it's noteworthy that for the opposite direction there is an easy solution:

A bit late, but today I noticed the special format masks 'TM9' and 'TME' which are described as "the text minimum number format model returns (in decimal output) the smallest number of characters possible." on https://docs.oracle.com/cloud/latest/db112/SQLRF/sql_elements004.htm#SQLRF00210.

It seems as if TM9 was invented just to solve this particular problem:

select to_char(1234.5678, 'TM9', 'NLS_NUMERIC_CHARACTERS=''.,''') from dual;

The result is '1234.5678' with no leading or trailing blanks, and a decimal POINT despite my environ containing NLS_LANG=GERMAN_GERMANY.WE8MSWIN1252, which would normally cause a decimal COMMA.

Upvotes: 3

Vincent Malgrat
Vincent Malgrat

Reputation: 67802

The following should work:

SELECT to_number(:x, 
                 translate(:x, '012345678-+', '999999999SS'), 
                 'nls_numeric_characters=''.,''') 
  FROM dual;

It will build the correct second argument 999.999999 with the efficient translate so you don't have to know how many digits there are beforehand. It will work with all supported Oracle number format (up to 62 significant digits apparently in 10.2.0.3).

Interestingly, if you have a really big string the simple to_number(:x) will work whereas this method will fail.

Edit: support for negative numbers thanks to sOliver.

Upvotes: 12

Søren Boisen
Søren Boisen

Reputation: 1706

If you are doing a lot of work per session, an option may be to use ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,' at the beginning of your task.

Of course, if lots of other code is executed in the same session, you may get funky results :-) However we are able to use this method in our data load procedures, since we have dedicated programs with their own connection pools for loading the data.

Upvotes: 3

Related Questions