Forum Stats

  • 3,768,172 Users
  • 2,252,755 Discussions
  • 7,874,481 Comments

Discussions

Position of next numeric value in Oracle SQL

User_HUQ7H
User_HUQ7H Member Posts: 2 Green Ribbon

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

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,205 Red Diamond
    Accepted Answer

    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

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,205 Red Diamond
    Accepted Answer

    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
  • User_HUQ7H
    User_HUQ7H Member Posts: 2 Green Ribbon

    Thank you Frank! This helped me find my solution.