I need help with a SQL query to check if a numeric column contains a number in ascending or descending order.

eg. 123456 or 654321

This is to avoid people entering some random values in a customer phone number column, users are required to give valid phone number input.

I want to achieve this without using a function.

CodePudding user response：

To check if it's like a sequence of digits?
Then you can simply use LIKE

select num
, cast(case 
  when '12345678901234567890' like concat('%',num,'%')
   then 1 
  when '98765432109876543210' like concat('%',num,'%')
   then 1 
  else 0 end as bit) as isSequence
from (values 
(123456),
(765432),
(797204)
) nums(num)

Or use CHARINDEX

select num
, cast(case 
  when 0 < charindex(concat(num,''),'_12345678901234567890') then 1 
  when 0 < charindex(concat(num,''),'_98765432109876543210') then 1 
  else 0 end as bit) as isSequence
from (values 
(0123456),
(765432),
(797204)
) nums(num)

db<>fiddle here