Oracle extract variable number from string

Question

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.

Example:

D-3-J32P232

-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.

It could range from:

D-3-J1P232 to D-3-J322P2342

The numbers after the second and third letters can be any number of length. Is there any way to do this?

josephj1989 · Accepted Answer · 2011-06-29 05:43:19Z

3

This is simpler and gets both the numbers for the range

select  substr( REGEXP_SUBSTR('D-3-J322P2342','[A-Z][0-9]+',1,1),2),
             substr( REGEXP_SUBSTR('D-3-J322P2342','[A-Z][0-9]+',1,2),2)
from dual

answered Jun 29, 2011 at 5:43

josephj1989

9,7299 gold badges50 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DCookie · Accepted Answer · 2011-06-29 04:22:28Z

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;

A test of your sample data:

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.

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);

Your sample data:

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

Gary Myers · Accepted Answer · 2011-06-29 00:19:07Z

1

REGEXP_SUBSTR would do the job

answered Jun 29, 2011 at 0:19

Gary Myers

35.5k4 gold badges52 silver badges77 bronze badges

Collectives™ on Stack Overflow

Oracle extract variable number from string

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related