Oracle 从字符串中提取变量数 [英] Oracle extract variable number from string

查看:46
本文介绍了Oracle 从字符串中提取变量数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我希望从查询中长度可变的混合字母数字字符串中提取特定数字.我需要这个来计算基于该数字的范围.我正在使用 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-J1P232D-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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆