如何使用非默认NLS_NUMERIC_CHARACTERS在Oracle PL/SQL中有效地将文本转换为数字? [英] How to efficiently convert text to number in Oracle PL/SQL with non-default NLS_NUMERIC_CHARACTERS?

问题描述

我试图在PL/SQL中找到一种有效的通用方法，将字符串转换为数字，而NLS_NUMERIC_CHARACTERS设置的本地设置是无法预测的-最好不要碰它.输入格式为编程标准"123.456789"，但小数点两侧的数字位数未知.

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接受第三个参数，但是在这种情况下，您也要指定第二个参数，并且没有默认"的格式规范...

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...

还有另一种使用PL/SQL的方式:

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;

完全符合我的要求，但是如果您在查询中进行多次则似乎效率不高.您无法缓存v_decimal的值(一次获取并存储在程序包变量中)，因为它不知道是否更改了NLS_NUMERIC_CHARACTERS的会话值，然后它将再次中断.

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.

我在俯视什么吗?还是我太担心了，Oracle的效率要比我认为的要高得多?

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?

推荐答案

以下方法应该起作用:

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

它将使用有效的translate构建正确的第二个参数999.999999，因此您不必事先知道有多少个数字.它将与所有受支持的Oracle数字格式一起使用(在10.2.0.3中显然最多可以有62个有效数字).

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).

有趣的是，如果字符串很大，则简单的to_number(:x)将起作用，而此方法将失败.

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

感谢 sOliver 支持负数.

这篇关于如何使用非默认NLS_NUMERIC_CHARACTERS在Oracle PL/SQL中有效地将文本转换为数字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！