How to solve the 123456789 Equation Puzzle in SQL
May 30th, 2008 By Frank Zhou
The following is an interesting puzzle posted by Usenet rec-puzzles.org archive:
In how many ways can “.” be replaced with “+”, “-”, or “” (concatenate) in .1.2.3.4.5.6.7.8.9 = 1 to form a correct equation?
——————————————-SQL Solution————————-
SELECT str_eq || ' = ' || output as output, count(*) over ( ) as counter
FROM
(select str_eq, XMLQuery( str_eq RETURNING CONTENT).getnumberval() output
FROM
(
WITH DATA AS
(
SELECT doc.extract('/l/text()').getStringVal() chars
FROM
TABLE(xmlSequence(extract(XMLType('<doc><l>'||
replace('+,-, ',',','</l><l>')||'</l></doc>'),'/doc/l'))) doc
)
SELECT replace(sys_connect_by_path(chars||to_char(LEVEL),'.'), '.') as str_eq
FROM ( SELECT chars from data)
WHERE LEVEL = 9
CONNECT BY LEVEL <= 9
)
)
where output = 1;
OUTPUT COUNTER
-------------------------------------- ----------
+1+2+3+4+5-6-7+8-9 = 1 69
+1+2+3+4-5+6+7-8-9 = 1 69
+1+2+3+45-67+8+9 = 1 69
+1+2+3-4-5-6-7+8+9 = 1 69
+1+2-3+4-5-6+7-8+9 = 1 69
+1+2-3-4+5+6-7-8+9 = 1 69
+1+2-3-4+5-6+7+8-9 = 1 69
+1+2-34+56-7-8-9 = 1 69
+1+23+4-5+67-89 = 1 69
+1+23+45-67+8-9 = 1 69
+1+23-4-5-6-7+8-9 = 1 69
OUTPUT COUNTER
-------------------------------------- ----------
+1+23-45-67+89 = 1 69
+1-2+3+4-5+6-7-8+9 = 1 69
+1-2+3+4-5-6+7+8-9 = 1 69
+1-2+3-4+5+6-7+8-9 = 1 69
+1-2+34-56+7+8+9 = 1 69
+1-2-3+4+5+6+7-8-9 = 1 69
+1-2-3-4+5-6-7+8+9 = 1 69
+1-2-3-4-5+6+7-8+9 = 1 69
+1-2-3-45+67-8-9 = 1 69
+1-23+4+5+6+7-8+9 = 1 69
+1-23+45+67-89 = 1 69
OUTPUT COUNTER
-------------------------------------- ----------
+1-23-4+5-67+89 = 1 69
+1-23-45+67-8+9 = 1 69
+12+34+5-67+8+9 = 1 69
+12-34+5-6+7+8+9 = 1 69
-1+2+3+4+5-6-7-8+9 = 1 69
-1+2+3+4-5+6-7+8-9 = 1 69
-1+2+3-4+5+6+7-8-9 = 1 69
-1+2-3+4-5-6-7+8+9 = 1 69
-1+2-3-4+5-6+7-8+9 = 1 69
-1+2-3-4-5+6+7+8-9 = 1 69
-1+23+4+5-6-7-8-9 = 1 69
OUTPUT COUNTER
-------------------------------------- ----------
-1+23+45-67-8+9 = 1 69
-1+23-4+5+67-89 = 1 69
-1+23-4-5-6-7-8+9 = 1 69
-1-2+3+4-5-6+7-8+9 = 1 69
-1-2+3-4+5+6-7-8+9 = 1 69
-1-2+3-4+5-6+7+8-9 = 1 69
-1-2-3+4+5+6-7+8-9 = 1 69
-1-2-3-4-5+6-7+8+9 = 1 69
-1-23+4+5+6-7+8+9 = 1 69
-12+34-5-6+7-8-9 = 1 69
1+2+3+4+5-6-7+8-9 = 1 69
OUTPUT COUNTER
-------------------------------------- ----------
1+2+3+4-5+6+7-8-9 = 1 69
1+2+3+45-67+8+9 = 1 69
1+2+3-4-5-6-7+8+9 = 1 69
1+2-3+4-5-6+7-8+9 = 1 69
1+2-3-4+5+6-7-8+9 = 1 69
1+2-3-4+5-6+7+8-9 = 1 69
1+2-34+56-7-8-9 = 1 69
1+23+4-5+67-89 = 1 69
1+23+45-67+8-9 = 1 69
1+23-4-5-6-7+8-9 = 1 69
1+23-45-67+89 = 1 69
OUTPUT COUNTER
-------------------------------------- ----------
1-2+3+4-5+6-7-8+9 = 1 69
1-2+3+4-5-6+7+8-9 = 1 69
1-2+3-4+5+6-7+8-9 = 1 69
1-2+34-56+7+8+9 = 1 69
1-2-3+4+5+6+7-8-9 = 1 69
1-2-3-4+5-6-7+8+9 = 1 69
1-2-3-4-5+6+7-8+9 = 1 69
1-2-3-45+67-8-9 = 1 69
1-23+4+5+6+7-8+9 = 1 69
1-23+45+67-89 = 1 69
1-23-4+5-67+89 = 1 69
OUTPUT COUNTER
-------------------------------------- ----------
1-23-45+67-8+9 = 1 69
12+34+5-67+8+9 = 1 69
12-34+5-6+7+8+9 = 1 69
69 rows selected.