TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
 Specialized Dev Zones Research Center eBook Library .NET Java C++ Web Dev Architecture Database Security Open Source Enterprise Mobile Special Reports 10-Minute Solutions DevXtra Blogs Slideshow

# A Numbers Game to Test Your SQL Skills : Page 2

## Programming is not only remembering classes or utilities. Developing logical programming skills is just as important. See how you can hone your SQL skills by solving an abstract numbers problem.

 by Alex Kozak
 Jul 10, 2006
 Page 2 of 3

#### The Answer Lies in SQL

If you think about it, bipartite numbers are nothing more than two-digit numbers from 10 to 98, excluding cases where the first and second digits are equal (11, 22, 33, 44, 55, 66, 77, 88, 99). The pool of two-digit numbers can be extended by the digit's replication. Theoretically, you can replicate each digit any number of times, but in real world, the degree of replication (number of replicated digits) will be limited by data type. This leads to two interesting tasks for solving the problem:
1. Generating a set of replicated digits from 0 to 9
2. Finding all bipartite numbers in the specified range

The solution in Listing 1 accomplishes the first task:

``````
Listing 1. Generate a Set of Replicated Digits from 0 to 9

SET NOCOUNT ON
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.t1')
AND type in (N'U'))
DROP TABLE dbo.t1
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.t2')
AND type in (N'U'))
DROP TABLE dbo.t2
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.t3')
AND type in (N'U'))
DROP TABLE dbo.t3

CREATE TABLE t1(c1 int);
INSERT INTO t1
SELECT 1 AS C1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9;

CREATE TABLE t2(c1 VARCHAR(20));
INSERT INTO t2
SELECT '0' AS c1
UNION ALL
SELECT '1'
UNION ALL
SELECT '2'
UNION ALL
SELECT '3'
UNION ALL
SELECT '4'
UNION ALL
SELECT '5'
UNION ALL
SELECT '6'
UNION ALL
SELECT '7'
UNION ALL
SELECT '8'
UNION ALL
SELECT '9';

SELECT REPLICATE(t2.c1, t1.c1) sequence
INTO t3
FROM t1 CROSS JOIN t2
ORDER BY 1

SELECT * FROM t3

Reulst:

Sequence
---------
0
00
000
0000
00000
000000
0000000
00000000
000000000
1
11
111
1111
11111
111111
1111111
11111111
111111111
. . . . . . .
. . . . . . .
. . . . . . .
. . . . . . .
8
88
888
8888
88888
888888
8888888
88888888
888888888
9
99
999
9999
99999
999999
9999999
99999999
999999999

(90 row(s) affected)
``````

The maximum number of replicated digits in my example is 9 (equal to the biggest number inserted into table t1). If you want to replicate more digits, then insert more numbers (10, 11, and so on) into table t1.

Now, you can proceed and find all possible combinations of two distinct rows in table t3. You can do this using cross-join by joining table t3 to itself in a self-join (see Listing 2):

``````
Listing 2. How to Find Bipartite Numbers

SELECT  (t3.sequence + t4.sequence) AS [Bipartite Numbers]
FROM t3 CROSS JOIN t3 AS t4
WHERE LEFT(t3.sequence + t4.sequence, 1) <> '0'
AND LEFT(t3.sequence, 1) <> LEFT(t4.sequence, 1)
ORDER BY 1

Result:

Bipartite Numbers
------------------
10
100
1000
10000
100000
1000000
10000000
100000000
1000000000
110
1100
11000
110000
. . . . . . . .
. . . . . . . .
11111110000000
111111100000000
1111111000000000
111111110
1111111100
11111111000
111111110000
1111111100000
. . . . . . . .
. . . . . . . .
1111111119999
11111111199999
111111111999999
1111111119999999
11111111199999999
111111111999999999
. . . . . . . . . .
. . . . . . . . . .
99999999988888
999999999888888
9999999998888888
99999999988888888
999999999888888888

(6561 row(s) affected)
``````

As you can see, two predicates in the WHERE clause filter the cases where the number in the result set starts from zero and where the starting digits in the numbers that need to be combined are equal.