SQL Server - Finding Number patterns

Question

I've been looking at this this for the last hour and just can't seem to find a way to do it, I'm sure its pretty simple but my google and reading skills have failed me.

All I need to do is to find ascending and descending numerical patterns in a field.

Like in this pseudo-SQL Code:

select * where col = '123456' or '23456' or '7654' or '987654321'

Most of the pattern methods using LIKE seem to be around placement of characters/numbers rather than the specific ordering,

I've started trying to create a query than takes the first character and compares it to the next one but this seems really ineffective and inefficient as it would need to take each field in the column run the query and return it if it matches.

I've managed to find a way to get it if its a repeated character but not if its an increase or decrease.

Any help would be greatly appreciated.

Hi Tim, here's a small sample and expected output 4141243, 4290577, 98765432, 78635389, 4141243, 22222, 4290046, 55555555, 4141243, 6789, 77777, 45678, 4294461, 55555, 4141243, 5555 so the return from this would be 6789 and 98765432, there is no length criteria (other than the potential Maximum of 12 characters but the number can be between 1-12 characters long) edit -sorry thats a bit of an eyesore to read — Litation
– Litation, Commented Jul 29, 2016 at 10:02

Maksim.T · Accepted Answer · 2016-07-29 11:14:13Z

2

You can put regular expression inside your LIKE quotes. Ascending:

^(?=\d{4,10}$)1?2?3?4?5?6?7?8?9?0?$

Descending:

^(?=\d{4,10}$)9?8?7?6?5?4?3?2?1?0?$

d{4,10} here is possible value length, between 4 and 10 symbols.
Won't be fast, most likely.

You can check how it works on http://rubular.com/.

Edit: Sorry, I forgot to mention you will have to do a MS SQL Server CLR integration first. By default, MSSQL Server does not fully support RegEx.

This article describes how to create and use extensions for the LIKE (Transact-SQL) clause that supports Regular Expressions.

http://www.codeproject.com/Articles/42764/Regular-Expressions-in-MS-SQL-Server

edited Jul 29, 2016 at 11:14

answered Jul 29, 2016 at 10:03

Maksim.T

5414 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Litation Over a year ago

Hi, Thanks I've given it a go and it doesn't return any results, however it may be I need to change something for it to use Regex its not something I've used myself before as I didn't think it was supported in TSQL

Martin Brown Over a year ago

Won't those match sequences with gaps in? So ^(?=\d{4,10}$)1?2?3?4?5?6?7?8?9?0?$ matches 24567.

John Cappelletti · Accepted Answer · 2016-07-29 13:09:58Z

0

Another option could be something like this:

Declare @Table table (col int)
Insert into @Table values
(4141243),(4290577),(98765432),(78635389),(4141243),(22222),(4290046),(55555555),(4141243),(6789),(77777),(45678),(4294461),(55555),(4141243),(5555)

Declare @Num table (Num int);Insert Into @Num values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)

Select Distinct A.*
  From @Table A
  Join (
        Select Patt=replicate(Num,3) from @Num
        Union All
        Select Patt=right('000'+cast((Num*100+Num*10+Num)+12 as varchar(5)),3) from @Num where Num<8
        Union All
        Select Patt=reverse(right('000'+cast((Num*100+Num*10+Num)+12 as varchar(5)),3)) from @Num where Num<8
       ) B on CharIndex(Patt,cast(col as varchar(25)))>0

Returns

**

Think RUMMY 500. A groups or runs of 3. For example 123 or 321 or 333 would be a hit.

**

edited Jul 29, 2016 at 13:09

answered Jul 29, 2016 at 12:52

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

1 Comment

Litation Over a year ago

Thanks, I haven't had a chance to try this yet but it looks sound and solves both of the rules I'm looking for in one solution.

Collectives™ on Stack Overflow

SQL Server - Finding Number patterns

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related