I need a SELECT that selects fields consisting of consecutive digits

Question

I need a SELECT that selects records consisting of consecutive digits.

In a T-SQL select I need to select all records in the column 'phone' where all digits are consecutive ascending or descending but if there is a non-consecutive digit, it does not detect it.

Example: 1234567 is detected by the select.

1234568 is NOT detected by the select because there is no 7 between 6 and 8.

876543 is detected by the select.

1245689 is not detected by the select because all the digits are not directly consecutive.

Thank you!

Answer 1

This is pretty easy to accomplish:

SELECT  *
,   CASE WHEN LEN(REPLACE('1234567890', phone, '')) <> 10 OR LEN(REPLACE('1234567890', REVERSE(phone), '')) <> 10 THEN 1 ELSE 0 END AS detected
FROM    (
    
    VALUES  (1234567, 1)
    ,   (1234568, 0)
    ,   (876543, 1)
    ,   (1245689, 0)
    ) x (phone, expected)

You take the 1234...0 string and replace it with the phone number. If replace "succeeds", it means that phone has consecuitive numbers. To handle descending numbers, you also test for REVERSE of the phone number.

You can adapt the code if 0 should be handled in some other way.

Output:

phone	expected	detected
1234567	1	1
1234568	0	0
876543	1	1
1245689	0	0