Oracle 从字符串中提取变量数 [英] Oracle extract variable number from string
问题描述
我希望从查询中长度可变的混合字母数字字符串中提取特定数字.我需要这个来计算基于该数字的范围.我正在使用 Oracle.
I'm looking to extract a specific number out of a string of mixed alphanumeric characters that are variable in length in a query. I will need this in order to calculate a range based off that number. I'm using Oracle.
示例:
D-3-J32P232
-我至少需要抓住 J32,而且很可能甚至是该字符串中的 32.这个数字范围可以随时更改.
-I need to grab the J32 at least, and most likely even the 32 out of that string. This range of numbers can change at any given time.
范围可以是:
D-3-J1P232到D-3-J322P2342
第二个和第三个字母后面的数字可以是任意长度的数字.有没有办法做到这一点?
The numbers after the second and third letters can be any number of length. Is there any way to do this?
推荐答案
REGEXP_SUBSTR 可以工作(11g 版本):
REGEXP_SUBSTR could work (11g version):
SELECT REGEXP_SUBSTR('D-3-J322P2342','([A-Z]+-\d+-[A-Z]+)(\d+)',1,1,'i',2) num
FROM dual;
对样本数据的测试:
SQL> SELECT REGEXP_SUBSTR('D-3-J322P2342',''([A-Z]+-\d+-[A-Z]+)(\d+)',1,1,'i',2) num
2 FROM dual;
NUM
---
322
SQL>
这将接受任何大小写字母字符串,后跟一个破折号,然后是一个或多个数字,然后是一个破折号,然后是另一个任何大小写的字母字符串,然后是您感兴趣的号码.
This will accept any case alpha string, followed by a dash, followed by one or more digits, followed by a dash, followed by another any case alpha string, then your number of interest.
在 10g REGEXP_REPLACE 中,这有点不那么直接,因为他们直到 11g 才添加引用子表达式的能力:
In 10g REGEXP_REPLACE, it's bit less straightforward, as they did not add the ability to reference subexpressions until 11g:
SELECT REGEXP_SUBSTR(str,'\d+',1,1) NUM
FROM (SELECT REGEXP_REPLACE('D-3-J322P2342','([A-Z]+-\d+-[A-Z]+)','',1,1,'i') str
FROM dual);
您的样本数据:
SQL> SELECT REGEXP_SUBSTR(str,'\d+',1,1) NUM
2 FROM
(SELECT REGEXP_REPLACE('D-3-J322P2342','([A-Z]+-\d+-[A-Z]+)','',1,1,'i') str
3 FROM dual);
NUM
---
322
这篇关于Oracle 从字符串中提取变量数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!