Oracle sql retrieving consecutive numbers in a string

Question

We have many fake account numbers in the database table. I would like to find those which are consecutive incremental numbers. For example, 123456789 and 12345 but do not include imbedded consecutive numbers. For example, 1234598 would not be a candidate.

select acct_nbr 
from account 
where acct_nbr like ('12345%', '5432%');

I want 12345678 but not 123458888 and similarly 54321 but not 54329.

How should I get? Is there any regexp_like which I can use?

Dmitry Nikiforov · Accepted Answer · 2014-03-17 20:19:56Z

5

Your maximum consecutive number starting with 1 is 123456789 and 987654321 in reverse order.

SQL> with t (x) as (
  2  select '12345678' from dual union all
  3  select '2345678' from dual union all
  4  select '237451678' from dual union all
  5  select '123458888' from dual union all
  6  select '54321' from dual union all
  7  select '54329' from dual
  8  )
  9  select * from t where regexp_like('123456789',x) or regexp_like('987654321',x)
 10  /

X                                                                               
---------                                                                       
12345678                                                                        
2345678                                                                         
54321

answered Mar 17, 2014 at 20:19

Dmitry Nikiforov

3,06816 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

Oracle sql retrieving consecutive numbers in a string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related