使用Oracle函数从字符串中提取数字

Question

我需要创建一个Oracle数据库函数，以字符串作为参数。该字符串包含字母和数字。我需要从这个字符串中提取所有的数字。例如，如果我有一个像RO1234这样的字符串，我需要能够使用一个函数，比如extract_number('RO1234')，结果将是1234。

更具体地说，这是使用此函数的SQL查询的类型。

SELECT DISTINCT column_name, extract_number(column_name) 
FROM table_name
WHERE extract_number(column_name) = 1234;

问题：我该如何将此类函数添加到我的Oracle数据库中，以便能够像上面的示例一样使用它，使用任何Oracle SQL Developer或SQLTools客户端应用程序？

- Vali S

---

你可以使用内置函数 regex_replace，例如 SELECT REGEXP_REPLACE(column_name,'[[:alpha:]]') from tbl。至少你应该在谷歌上搜索一下，参考这个链接(https://community.oracle.com/thread/598281)，Oracle社区提供了一些有用的方法。 - Vivek S.

我需要一个好的例子。我不擅长Oracle。 - Vali S

我需要从这个字符串中提取所有的数字 - 如果输入字符串包含多个数字，应该怎么办？例如，对于输入'1234abc5678'，你的函数应该返回什么？ - Frank Schmitt

@FrankSchmitt，鉴于该列存储的数值格式，无需考虑这种可能性。 - Vali S

您的示例查询似乎存在错误，即在 table_name 周围使用了单引号（可能还在 column_name 周围不正确地使用了单引号）。此外，当您已经在 WHERE 条件中指定了提取的数字时，为什么还要将其列出在列列表中呢？ - Hilarion

Answer 1

您可以使用 REGEXP_REPLACE 来从字符串中删除所有非数字字符：

select regexp_replace(column_name, '[^0-9]', '')
from mytable;

或者

select regexp_replace(column_name, '[^[:digit:]]', '')
from mytable;

当然可以编写一个名为extract_number的函数。不过，似乎只包含一个函数调用的函数有点过度设计了。

create function extract_number(in_number varchar2) return varchar2 is
begin
  return regexp_replace(in_number, '[^[:digit:]]', '');
end; 

- Thorsten Kettner

---

我不需要修改SQL查询，我需要一个可以调用的函数。 - Vali S

REGEXP_REPLACE 是你需要调用的函数，以提取数字。 - Thorsten Kettner

然而，我已添加了一个函数让您可以调用regexp_replace。 - Thorsten Kettner

是的，过度设计就是我的另一个名字。谢谢。 - Vali S

@ABHISHEK D：你是在寻找 select regexp_replace(column_name, '[^0-9]', '') as my_numeric_value from mytable; 吗？ - Thorsten Kettner

Answer 2

您可以使用正则表达式从字符串中提取数字。让我们来看一下。假设这是一个混合文本和数字的字符串'stack12345overflow569'，这个正则表达式应该可以工作：

select regexp_replace('stack12345overflow569', '[[:alpha:]]|_') as numbers from dual;

将返回“12345569”。

你也可以使用这个：

select regexp_replace('stack12345overflow569', '[^0-9]', '') as numbers,
       regexp_replace('Stack12345OverFlow569', '[^a-z and ^A-Z]', '') as characters
from dual

该函数将返回数字的“12345569”和字符的“StackOverFlow”。

- Parasram Pawar

---

这仍然返回文本（字符类型），而不是数字。将其放置在问题中显示的示例查询中也会使答案更加清晰明了。 - Hilarion

如何从最后一个字符获取569，即数字？ - undefined

Answer 3

这对我有用，我只需要字符串中的第一个数字：

TO_NUMBER(regexp_substr(h.HIST_OBSE, '\.*[[:digit:]]+\.*[[:digit:]]*'))

该字段包含以下字符串："(43 Paginas) REGLAS DE PARTICIPACION".

结果字段：43

- Alejandro Ovalle

---

提取字符串中第一组数字的更简单方法是选择regexp_substr(h.HIST_OBSE,'[0-9]+','')。 - George Joseph

Answer 4

从字符串中提取字符

SELECT REGEXP_REPLACE(column_name,'[^[:alpha:]]') alpha FROM DUAL 

- Assaduzzaman Sarker

---

您的回答可以通过提供更多支持性信息来改进。请通过添加引文或文献等进一步细节进行[编辑]，以便他人可以确认您的答案正确无误。您可以在帮助中心中找到有关撰写好答案的更多信息。 - Community

Answer 5

如果您正在寻找带有正确小数位的字符串的第一个数字，可以尝试使用regexp_substr函数，如下所示：

regexp_substr('stack12.345overflow', '\.*[[:digit:]]+\.*[[:digit:]]*')

- Iqbal Singh

---

这里有一个错别字（文本周围的单引号重复了），并且仍然返回文本（字符类型），而不是数字。将其放置在问题中显示的示例查询中，答案也会更加清晰明了。 - Hilarion

Answer 6

为了从字符串'A0807'中提取月份和年份，我在PL/SQL中执行了以下操作：

DECLARE
    lv_promo_code VARCHAR2(10) := 'A0807X';
    lv_promo_num VARCHAR2(5);
    lv_promo_month NUMBER(4);
    lv_promo_year NUMBER(4);
    BEGIN
    lv_promo_num := REGEXP_SUBSTR(lv_promo_code, '(\d)(\d)(\d)(\d)');

lv_promo_month := EXTRACT(month from to_date(lv_promo_num, 'MMYY'));
DBMS_OUTPUT.PUT_LINE(lv_promo_month);
lv_promo_year := EXTRACT(year from to_date(lv_promo_num, 'MMYY'));
DBMS_OUTPUT.PUT_LINE(lv_promo_year);
END;

- nsola