Oracle SQL to find out sequential numbers from a number field

If I were going to tackle that problem, I'd build a table of all the numerical sequences that qualify. It appears you have both increasing and decreasing. Either way. It's a limited number. I'd then simply do an inner join between the column I'm interested in and my table of qualifying sequences. This avoids row by row operations and simplifies the SQL involved.

There are a number of ways you could tackle this with logical code, fancy UDFs etc, but in the end I think a join, especially leveraging proper indexes would be "the best". I interpret "best" as most efficient use of resources and speed of return.

Where this would no longer scale is if you wanted to also show sequences into double digits. I.e. 123456789101112

Oracle SQL support regex functions, use something like: ^(?=\d{15}$)0?1?2?3?4?5?6?7?8?9? based on: Regular Expression for matching a numeric sequence?

I am not great at Regex, and I am unsure of exactly why you're doing this, so can't give a better solution.

Picking up @RThomas's idea here (upvoted!): having a table that contains all sequences you want to detect, and writing an inner join to the table you want to check. Maybe the following code snippets will be of help (tested with Oracle 12c).

Table that will contain all relevant patterns.

drop table patterns ;

create table patterns (
  pattern number primary key
);

Anonymous block (execute once)

-- find patterns in the 2 constants (ascending/descending)
-- and insert them into the patterns table
declare
  ascpattern constant char( 9 ) := '123456789' ;
  descpattern constant char( 9 ) := '987654321' ;
begin
  for length in 2 .. 9 
  loop
    for offset in 1 .. 8 
    loop
      if length + offset <= 10 then
        -- dbms_output.put_line ( substr( ascpattern, offset, length ) ) ;
        -- dbms_output.put_line ( substr( descpattern, offset, length ) ) ;
        insert into patterns ( pattern ) 
          values ( to_number( substr( ascpattern, offset, length ) ) ) ;
        insert into patterns ( pattern ) 
          values ( to_number( substr( descpattern, offset, length ) ) ) ;
      end if ;
    end loop ;
  end loop ;
end ;
/

Patterns table

select * from patterns ;

   PATTERN
----------
        12  -- contained in ascpattern
        21  -- contained in descpattern
        23
        32
        34
...
   9876543
  12345678  -- contained in ascpattern
  23456789  -- contained in ascpattern
  87654321  -- contained in descpattern
  98765432  -- contained in descpattern
 123456789  -- contained in ascpattern
 987654321

72 rows selected.

Testing: table containing 100000 random integers.

drop table randomnumbers ;

create table randomnumbers
as
select trunc( dbms_random.value ( 0, 999999 ) ) randomnumber
from dual
connect by level <= 100000 ;

select * from randomnumbers fetch first 6 rows only ;


RANDOMNUMBER
------------
      529903
      653752
      339231
       42871
      599376
      381709

Detect patterns in the random numbers table by using an inner join. (Your resultset will look different, of course. )

select
  pattern
, randomnumber
from patterns P
  join randomnumbers R on P.pattern = R.randomnumber 
order by pattern ;


   PATTERN RANDOMNUMBER
---------- ------------
        76           76
        87           87
       456          456
      3456         3456
     54321        54321
     56789        56789