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

Question

我有一个由数字、小数点、字母和空格组成的5个字符字符串。如果字符串中的所有字符都是数字，我想将该字符串转换为数字（整数）。

• 不允许使用小数点
• 不允许使用+/-符号
• 不能在中间使用空格，但可以在两端使用

提前感谢。

- Mayur

Answer 1

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

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 

编辑:

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

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

- Sai Kalyan Kumar Akshinthala

---

如果字符串中包含字母、小数点，而我想仅在字符串中存在数字时将其转换为整数，该怎么办？ - Mayur

Answer 2

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

- CloudyMarble

---

如何检查字符串是否满足上述条件的有效性？ - Mayur

使用 Instr 函数在转换字符串之前检查是否存在非有效字符。 - CloudyMarble

Answer 3

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.

- user272735

Answer 4

你试过使用 CAST(var AS NUMBER) 吗？

- StilesCrisis

Answer 5

可以尝试使用这个替代方案

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

假设输入为+1234.34。

- Manoj

Answer 6

假设您正在使用变量foo_code进行工作。

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

分解：

• 删除前导和尾随空格
• 将任何内部空格转换为“x”-考虑测试用例“1234 098”（即简单字符串打破第三个条件）
• 将任何数字转换为空格
• 删除前导和尾随空格
• 如果所有内容都是数字，则应该剩下一个空字符串，在Oracle术语中为NULL

- Steve Bosman