Series of numbers with required pair of Digits - SQL

Question

Series of numbers with required pair of Digits - SQL

Rehan Ahmad

Right there with Babe

Points: 791
More actions
November 11, 2016 at 2:24 am

#401026

Hi Everyone,
I need to compile/generate a series of 7 digits on a specific criteria where 6 digits/strings are same and also later find the required criteria for that series.
Series of 7 digits is required in this way that there must be at least 6 same digits i.e. there might be
0000001
0000002
.....
1000000
.....
9777777
9888888
etc.
The range of series is starting from 0000001 and ending with maximum possiblity of 9888888. There is no restrict of data type. Further, could it be possible in other way that we generate a series starting from 0000001 to 9888888 and find those where 6 digits/strings are same.
Thanks,
TechPro

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 1

Do the six matching digits have to be together? ie must the string be of the form xxxxxxy / yxxxxxx or can it be xxyxxxx?

Thomas Rushton
blog: https://thelonedba.wordpress.com

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 2

Here is a quick suggestion

😎

USE TEEST;

GO

SET NOCOUNT ON;

;WITH CT(CN) AS (SELECT CN FROM (VALUES ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9')) X(CN))

SELECT

REPLICATE(C.CN,6) + CX.CN AS GEN_SEQ

FROM CT C

CROSS APPLY CT CX

WHERE CX.CN <> C.CN

UNION ALL

SELECT

CX.CN + REPLICATE(C.CN,6)

FROM CT C

CROSS APPLY CT CX

WHERE CX.CN <> C.CN;

Output

GEN_SEQ

-------

1111110

2222220

3333330

4444440

5555550

6666660

7777770

8888880

9999990

0000001

2222221

3333331

4444441

5555551

6666661

7777771

8888881

9999991

0000002

1111112

3333332

4444442

5555552

6666662

7777772

8888882

9999992

0000003

1111113

2222223

4444443

5555553

6666663

7777773

8888883

9999993

0000004

1111114

2222224

3333334

5555554

6666664

7777774

8888884

9999994

0000005

1111115

2222225

3333335

4444445

6666665

7777775

8888885

9999995

0000006

1111116

2222226

3333336

4444446

5555556

7777776

8888886

9999996

0000007

1111117

2222227

3333337

4444447

5555557

6666667

8888887

9999997

0000008

1111118

2222228

3333338

4444448

5555558

6666668

7777778

9999998

0000009

1111119

2222229

3333339

4444449

5555559

6666669

7777779

8888889

0111111

0222222

0333333

0444444

0555555

0666666

0777777

0888888

0999999

1000000

1222222

1333333

1444444

1555555

1666666

1777777

1888888

1999999

2000000

2111111

2333333

2444444

2555555

2666666

2777777

2888888

2999999

3000000

3111111

3222222

3444444

3555555

3666666

3777777

3888888

3999999

4000000

4111111

4222222

4333333

4555555

4666666

4777777

4888888

4999999

5000000

5111111

5222222

5333333

5444444

5666666

5777777

5888888

5999999

6000000

6111111

6222222

6333333

6444444

6555555

6777777

6888888

6999999

7000000

7111111

7222222

7333333

7444444

7555555

7666666

7888888

7999999

8000000

8111111

8222222

8333333

8444444

8555555

8666666

8777777

8999999

9000000

9111111

9222222

9333333

9444444

9555555

9666666

9777777

9888888

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Here's an alternative, if the digits don't have to be consecutive. A change to make them only xxxxxxy / yxxxxxx should be easy.

WITH CT(CN) AS (SELECT CN FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) X(CN))

SELECT

STUFF( REPLICATE(C.CN,6) + ' ', CY.CN, 0, CX.CN)

FROM CT C

CROSS JOIN CT CX

CROSS JOIN CT CY

WHERE CX.CN <> C.CN

AND CY.CN BETWEEN 1 AND 7;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Rehan Ahmad Right there with Babe Points: 791 More actions · Answer 4

Thanks Eirikur Eiriksson & Luis Cazares,

It works.:-)

By this way, we made a series where either 6 digits are same. Did we could do its alterate in SQL 2000. Further, if we need to do its opposite i.e. we have a series of total 7 digits and requirment is to find and mark those numbers which contains 6 same digits either in xyyyyyy or yyyxyyy format. Then what will be the approach for this.

Sample:

Number Remarks

1222222 Six Digits

2204444 Not Six Digits

.......

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 5

> we have a series of total 7 digits and requirment is to find and mark those numbers which contains 6 same digits either in xyyyyyy or yyyxyyy format.

See if this helps

DECLARE @myTable TABLE(Num VARCHAR(10));

INSERT INTO @myTable(Num)

SELECT '1222222' UNION ALL

SELECT '2204444';

SELECT Num AS [Number],

CASE WHEN Num LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

AND (SUBSTRING(Num,2,6) = REPLICATE(SUBSTRING(Num,2,1),6) -- xyyyyyy or xxxxxxx

OR SUBSTRING(Num,1,3)+SUBSTRING(Num,5,3) = REPLICATE(SUBSTRING(Num,1,1),6)) -- yyyxyyy

THEN 'Six Digits'

ELSE 'Not Six Digits' END AS Remarks

FROM @myTable;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

Rehan Ahmad (11/14/2016)
Thanks Eirikur Eiriksson & Luis Cazares,
It works.:-)
By this way, we made a series where either 6 digits are same. Did we could do its alterate in SQL 2000. Further, if we need to do its opposite i.e. we have a series of total 7 digits and requirment is to find and mark those numbers which contains 6 same digits either in xyyyyyy or yyyxyyy format. Then what will be the approach for this.
Sample:
Number Remarks
1222222 Six Digits
2204444 Not Six Digits
.......

This is wrong. It violates 3rd normal form and your code will suffer in many ways further down the road. You should have two separate columns to 1) make searching easier and SARGable and 2) to take advantage of storing the data in the correct numeric datatype. The only time you should mush the data together is for display purposes. If you wanted to, you could use a persisted computed column for the mushing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)