CODEWITHSUNDEEP
stringoracle-databasesubstringsubstr
JoshG
JoshG

Reputation: 941

Oracle extract variable number from string

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?

Upvotes: 1

Views: 7954

Answers (3)

josephj1989
josephj1989

Reputation: 9709

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

Upvotes: 3

DCookie
DCookie

Reputation: 43533

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

Upvotes: 2

Gary Myers
Gary Myers

Reputation: 35401

REGEXP_SUBSTR would do the job

Upvotes: 1

Related Questions