How to find consecutive numbers like 123456 in a column in SQL Server-CodePudding

I am working on a SQL Server database and would like to find a number with a consecutive digits in a column.

Suppose I have below values in a column

column_A
--------
123456
345678
214562
457215
456789

I would like to get the below rows with consecutive digits in the results:

123456
345678
456789

I have tried this:

select * 
from Table1 
where column_A like '^([0-9a-z])\1 '

but the regex doesn't seem to work in SQL Server.

Any help is very appreciated.

CodePudding user response：

You can do it using recursive CTE like the following query.

declare @table table(column_A varchar(10))

insert into @table select '123456'
insert into @table select '345678'
insert into @table select '214562'
insert into @table select '457215'
insert into @table select '456789'


;with cte as (
          select  v.column_A os, v.column_A, 1 as m
          from @table v
          union all
          select os,stuff(column_A, 1, 1, ''),
                case when cast(left(column_A, 1) as int)  1 = cast(left(stuff(column_A, 1, 1, ''),1) as int)  then 1 else 0 end as m
          from cte
          where column_A > ''   
         )
    select os
    from cte
    where column_A <>'' and m=1
    group by os 
    having count(m) =len(os)

Output

os
-----
123456
345678
456789

CodePudding user response：

Maybe this would work

SELECT column_A FROM TABLE1 
where 
column_A =
CONCAT((SELECT LEFT(column_A,1) FROM TABLE1)),(SELECT LEFT(column_A,1) 
FROM TABLE1) 1,(SELECT LEFT(column_A,1) FROM TABLE1) 2,(SELECT 
LEFT(column_A,1) FROM TABLE1) 3,(SELECT LEFT(column_A,1) FROM TABLE1) 4, 
(SELECT LEFT(column_A,1) FROM TABLE1) 5)