Home > Mobile >  Find values that increment or decrement by 1 in SQL
Find values that increment or decrement by 1 in SQL

Time:06-11

I'm trying to find a way to find strings that are like '1234', '12345', '9876', '7654', etc. So values that have numbers that are sequential either up or down. Anywhere from 3 characters (ex. 123, 321) to 10 characters (0123456789, 9876543210)

INSERT INTO #TmpTbl 
Values ('12345')
,('45678')
,('44569')
,('987654')
,('748376')
,('123')
,('0123456789')
,('9876543210')

Expected Results

  • 12345
  • 45678
  • 987654
  • 123
  • 0123456789
  • 9876543210

CodePudding user response:

You can use LIKE to find the patterns. For example:

select * 
from t
where '0123456789' like concat('%', a, '%')
   or '9876543210' like concat('%', a, '%');

Result:

a
----------
12345
45678
987654
123
0123456789
9876543210

See example at db<>fiddle.

  • Related