SQL 使用 Oracle 函数从字符串中提取数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32861256/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Extract number from string with Oracle function
提问by Vali S
I need to create an Oracle DB function that takes a string as parameter. The string contains letters and numbers. I need to extract all the numbers from this string. For example, if I have a string like RO1234, I need to be able to use a function, say extract_number('RO1234'), and the result would be 1234.
我需要创建一个将字符串作为参数的 Oracle DB 函数。该字符串包含字母和数字。我需要从这个字符串中提取所有数字。例如，如果我有一个像RO1234的字符串，我需要能够使用一个函数，比如extract_number('RO1234')，结果将是1234。
To be even more precise, this is the kind of SQL query which this function would be used in.
更准确地说，这是使用此函数的 SQL 查询类型。
SELECT DISTINCT column_name, extract_number(column_name)
FROM table_name
WHERE extract_number(column_name) = 1234;
QUESTION: How do I add a function like that to my Oracle database, in order to be able to use it like in the example above, using any of Oracle SQL Developer or SQLTools client applications?
问题：我如何将这样的函数添加到我的 Oracle 数据库中，以便能够像上面的示例一样使用它，使用任何 Oracle SQL Developer 或 SQLTools 客户端应用程序？
回答by Thorsten Kettner
You'd use REGEXP_REPLACEin order to remove all non-digit characters from a string:
您将用于REGEXP_REPLACE从字符串中删除所有非数字字符：
select regexp_replace(column_name, '[^0-9]', '')
from mytable;
or
或者
select regexp_replace(column_name, '[^[:digit:]]', '')
from mytable;
Of course you can write a function extract_number. It seems a bit like overkill though, to write a funtion that consists of only one function call itself.
当然你可以写一个函数extract_number。不过，编写一个只包含一个函数调用本身的函数似乎有点矫枉过正。
create function extract_number(in_number varchar2) return varchar2 is
begin
return regexp_replace(in_number, '[^[:digit:]]', '');
end;
回答by Parasram Pawar
You can use regular expressionsfor extracting the number from string. Lets check it. Suppose this is the string mixing text and numbers 'stack12345overflow569'. This one should work:
您可以使用正则表达式从字符串中提取数字。让我们检查一下。假设这是混合文本和数字“stack12345overflow569”的字符串。这个应该有效：
select regexp_replace('stack12345overflow569', '[[:alpha:]]|_') as numbers from dual;
which will return "12345569".
这将返回“12345569”。
also you can use this one:
你也可以使用这个：
select regexp_replace('stack12345overflow569', '[^0-9]', '') as numbers,
regexp_replace('Stack12345OverFlow569', '[^a-z and ^A-Z]', '') as characters
from dual
which will return "12345569" for numbers and "StackOverFlow" for characters.
它将为数字返回“12345569”，为字符返回“StackOverFlow”。
回答by Iqbal Singh
If you are looking for 1st Number with decimal as string has correct decimal places, you may try regexp_substrfunction like this:
如果您正在寻找第一个带有小数的数字，因为字符串具有正确的小数位，您可以尝试这样的regexp_substr函数：
regexp_substr('stack12.345overflow', '\.*[[:digit:]]+\.*[[:digit:]]*')
