Position of next numeric value in Oracle SQL

User_HUQ7H

I have am trying to extract certain IDs from the comments column. Comments columns includes alpha numeric, only numeric, only alphabet values but I need to extract ID which will be 5 characters and plus.

Is there a code to select only records with numeric values which are consecutive like 19555? If numeric values are between characters like AB22CD33EF55 are not valid IDs.

I was think of a process of identifying first numeric value as T1 and next non-numerical value as T2. Then my filter set is going to be  (T2 = 0) OR ((T2 - T1) > 4). Need help to determine T2 or any alternative approach.

REGEXP_INSTR(COMMENTS, '[0-9]') == 4 (eg ACB19955)

COMMENTS

----------------

ABCDE

ACB19955

AB19CDEF

16448

TYU223IO

--17663

TY18667

AB15889CD

RESULTS

------------

19955

16448

17663

18667

15889

Frank Kulash

Hi,

Sorry, I'm not sure what you want.

If comments is a string, then

REGEXP_SUBSTR (comments, '\d{5,}')

returns the first all-digits substring that is at least 5 characters long. If you want the first substring that is exactly 5 characters long, then change {5,} to {5} .

I hope that answers your question. If not, post CREATE TABLE and INSERT statements for a little sample data, the exact results you want from that sample data, and an explanation of how you get those results from that data.

Frank Kulash

Hi,

Sorry, I'm not sure what you want.

If comments is a string, then

REGEXP_SUBSTR (comments, '\d{5,}')

returns the first all-digits substring that is at least 5 characters long. If you want the first substring that is exactly 5 characters long, then change {5,} to {5} .

I hope that answers your question. If not, post CREATE TABLE and INSERT statements for a little sample data, the exact results you want from that sample data, and an explanation of how you get those results from that data.

User_HUQ7H

Thank you Frank! This helped me find my solution.