user961743 user961743 - 11 days ago 5
SQL Question

How To Determine If Number is Sequential In Oracle SQL

This is a second part to a previous question I'm asking. I have a table of license numbers and am trying to determine if they are legitimate. One of the tests that I'm trying to run is if the numbers are sequential/counting pattern. What would be really helpful is if I can get a count of sequential characters, but I feel that is more difficult. For example:

LICENSE_NUMBER IS_COUNTING NUM_SEQ_CHARS
123456789 TRUE 9
123455678 FALSE 5
456789012 TRUE 9
12345 TRUE 5


Thanks for your help!

Answer
^(01(2345678901)*(2(3(4(5(6(7(8(90?)?)?)?)?)?)?)?)?
 |12(3456789012)*(3(4(5(6(7(8(9(01?)?)?)?)?)?)?)?)?
 |23(4567890123)*(4(5(6(7(8(9(0(12?)?)?)?)?)?)?)?)?
 |34(5678901234)*(5(6(7(8(9(0(1(23?)?)?)?)?)?)?)?)?
 |45(6789012345)*(6(7(8(9(0(1(2(34?)?)?)?)?)?)?)?)?
 |56(7890123456)*(7(8(9(0(1(2(3(45?)?)?)?)?)?)?)?)?
 |67(8901234567)*(8(9(0(1(2(3(4(56?)?)?)?)?)?)?)?)?
 |78(9012345678)*(9(0(1(2(3(4(5(67?)?)?)?)?)?)?)?)?
 |89(0123456789)*(0(1(2(3(4(5(6(78?)?)?)?)?)?)?)?)?
 |90(1234567890)*(1(2(3(4(5(6(7(89?)?)?)?)?)?)?)?)?)$

It will match any string of two or more digits, that consists entirely of sequential digits.

123456789     MATCH
123455678     NOT MATCH
456789012     MATCH
12345         MATCH

Demo: http://regexr.com/3enkb

Comments