database 如何在 PL/SQL 中将字符串转换为数字

How to convert a string to number in PL/SQL

提问by Mayur

I have 5 character string which can have numbers, decimal points, alphabets and spaces. I wanted to convert this string to a number (integer) if all the characters in string are numbers. i.e.

我有 5 个字符串，其中可以包含数字、小数点、字母和空格。如果字符串中的所有字符都是数字，我想将此字符串转换为数字（整数）。IE

• No decimal points allowed
• No +/- sign allowed
• Spaces are not allowed in between but they can be allowed at extremes

• 不允许小数点
• 不允许 +/- 符号
• 中间不允许有空格，但可以在极端情况下允许

Thanks in advance.

提前致谢。

回答by Sai Kalyan Kumar Akshinthala

Use To_Number Functionin PL/SQL to convert a string into number, see below for example.

使用PL/SQL 中的To_Number 函数将字符串转换为数字，示例如下。

to_number('1210.73', '9999.99') would return the number 1210.73 
to_number('546', '999') would return the number 546 
to_number('23', '99') would return the number 23 

EDIT:

编辑：

In PL/SQL you can check whether a string consists of numeric characters or not by using LENGTH, TRIMand TRANSLATEfunctions.

在 PL/SQL 中，您可以使用LENGTH、TRIM和TRANSLATE函数检查字符串是否由数字字符组成。

LENGTH(TRIM(TRANSLATE(string1, ' +-.0123456789', ' ')))

回答by CloudyMarble

to_numberfunction converts a string to a number.

to_number函数将字符串转换为数字。

回答by user272735

create or replace function is_int(p_str in varchar2) return number as
begin
  if regexp_instr(p_str, '^[[:space:]]*[[:digit:]]{1,5}[[:space:]]*$') > 0 then
    return 1;
  end if;

  return 0;
end;
/
show errors

with strings as (
  select '12345' as string from dual
  union all
  select '1234' as string from dual
  union all
  select '123' as string from dual
  union all
  select '12' as string from dual
  union all
  select '1' as string from dual
  union all
  select '01' as string from dual
  union all
  select '' as string from dual
  union all
  select '  345' as string from dual
  union all
  select '123  ' as string from dual
  union all
  select '12.45' as string from dual
  union all
  select '12 45' as string from dual
  union all
  select '12,45' as string from dual
  union all
  select '-1234' as string from dual
  union all
  select '+1234' as string from dual
  union all
  select 'A2345' as string from dual
)
select testcase, to_number(string)
from strings
where is_int(string) = 1
;

  TESTCASE TO_NUMBER(STRING)
---------- -----------------
         1             12345
         2              1234
         3               123
         4                12
         5                 1
         6                 1
         8               345
         9               123

8 rows selected.

create or replace function to_int(p_str in varchar2) return number as
begin
  if regexp_instr(p_str, '^[[:space:]]*[[:digit:]]{1,5}[[:space:]]*$') > 0 then
    return to_number(p_str);
  end if;

  return null;
end;
/
show errors

with strings as (
  select 1 as testcase, '12345' as string from dual
  union all
  select 2, '1234' as string from dual
  union all
  select 3, '123' as string from dual
  union all
  select 4, '12' as string from dual
  union all
  select 5, '1' as string from dual
  union all
  select 6, '01' as string from dual
  union all
  select 7, '' as string from dual
  union all
  select 8, '  345' as string from dual
  union all
  select 9, '123  ' as string from dual
  union all
  select 10, '12.45' as string from dual
  union all
  select 11, '12 45' as string from dual
  union all
  select 12, '12,45' as string from dual
  union all
  select 13, '-1234' as string from dual
  union all
  select 14, '+1234' as string from dual
  union all
  select 15, 'A2345' as string from dual
)
select testcase, '''' || string || '''' as string
from strings
where to_int(string) is not null
;

  TESTCASE STRING
---------- ---------------------
         1 '12345'
         2 '1234'
         3 '123'
         4 '12'
         5 '1'
         6 '01'
         8 '  345'
         9 '123  '

8 rows selected.

回答by StilesCrisis

Did you try CAST(var AS NUMBER)?

你试过 CAST(var AS NUMBER) 吗？

回答by Manoj

Also try this instead

也试试这个

select floor(to_number(TRANSLATE(' +1234.34','+-',' '))) from dual;

Assume +1234.34 is the input

假设 +1234.34 是输入

回答by Steve Bosman

Assuming you are working with a variable foo_code

假设您正在使用一个变量 foo_code

IF TRIM(TRANSLATE(TRANSLATE(TRIM(foo_code), ' ', 'x'), '0123456789', ' ')) IS NULL THEN
  foo_number := TO_NUMBER(foo_code);
END IF;

breaking it down:

分解它：

• Trim leading and trailing spaces
• Translate any internal spaces into an 'x' - think of the test case '1234 098' (i.e a simple string breaking the third condition)
• translate any digits into spaces
• Trim leading and trailing spaces
• If everything was numeric you should be left with an empty string, which in Oracle terms is NULL

• 修剪前导和尾随空格
• 将任何内部空格转换为 'x' - 想想测试用例 '1234 098'（即一个简单的字符串打破第三个条件）
• 将任何数字转换为空格
• 修剪前导和尾随空格
• 如果一切都是数字，你应该留下一个空字符串，在 Oracle 术语中是 NULL