sql - 在字符串中搜索子字符串并除以

Question

我有一个包含多个句子的 varchar2(5000 char) 列的表。在这样的句子中的某处显示了一个数字，然后是另一个数字。我想选择这些数字并将它们分开。不确定如何在 sqlplus oracle 11g 中执行此操作。

我想我应该使用像SUBSTR, REGEXP_SUBSTR, INSTR. 不确定哪个最适合这份工作。

列是这样的：

blah bahwl balwo hxkswl blahxhsh alshbhe NUM 40003.26 in 4 pieces. etc
bwh bahwl bafado hxkswl alshbhe NUM 6006.16 in 9 pieces. etc
badh baadfl balwo hxkswl blahxhsh alshbhe NUM 200 in 30 pieces. etc
bfda bdafl hxkswl NUM 33 in 4 pieces. etc
blcfh bfdwl bfdlwo alshbhe NUM 54545.01 in 700 pieces. etc

所以我想直接在NUM之后搜索并选择数字，然后选择“in”和“pieces”之间的数字，然后将这些数字相除。

select .... 40003.26 / 4 
select .... 6006,16 / 9 
select ....  200 / 30 

等等

任何帮助表示感谢

标签： sqlregexoracleoracle11goracle10g

Answer 1

您可以使用regexp_substr,regexp_replace与l/的贡献rtrim如下

with t1 as
 (select 'blah bahwl balwo hxkswl blahxhsh alshbhe NUM 40003.26 in 4 pieces. etc
             bwh bahwl bafado hxkswl alshbhe NUM 6006.16 in 9 pieces. etc
             badh baadfl balwo hxkswl blahxhsh alshbhe NUM 200 in 30 pieces. etc
             bfda bdafl hxkswl NUM 33 in 4 pieces. etc
             blcfh bfdwl bfdlwo alshbhe NUM 54545.01 in 700 pieces. etc' as str
    from dual),
t2 as
 (select regexp_substr(str, '(.*)pieces.*', 1, level) as str
    from t1
   cross join dual
  connect by level <= regexp_count(str, 'pieces')),
t3 as
 (select str,
         regexp_replace(str, '[^0-9]in[^0-9]', '/') str1,
         regexp_substr(str, '[^0-9]*') str2,
         regexp_substr(str, '[^0-9]*$') str3
    from t2)
select rtrim(ltrim(str1, str2), str3) as "Result String" from t3


Result String
-------------
40003.26/4
6006.16/9
200/30
33/4
54545.01/700

Demo