Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

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.


advertisement

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.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date