Categories

# le compte est bon

I am back from my vacations, I was at nice places in Switzerland like Rhone Glacier, underground lake of Saint-Leonard, Salt Mines of Bex, Rhine Waterfalls and more …

To keep up with the fun, here is a little quiz :

You have the numbers 1-3-4-6 and you need to achieve the number 24. The allowed operations are +, -, * and /

If I try to achieve 49 it is easy :
```SQL> / Enter value for n1: 1 old 14: (SELECT &n1 n new 14: (SELECT 1 n Enter value for n2: 3 old 17: SELECT &n2 n new 17: SELECT 3 n Enter value for n3: 4 old 20: SELECT &n3 n new 20: SELECT 4 n Enter value for n4: 6 old 23: SELECT &n4 n new 23: SELECT 6 n Enter value for result: 49 old 143: ) = &result new 143: ) = 49```

``` result ------------------------------------ (4+3)*(6+1) (3+4)*(6+1) (6+1)*(4+3) (1+6)*(4+3) (6+1)*(3+4) (1+6)*(3+4) (4+3)*(1+6) (3+4)*(1+6) 8 rows selected. ```

`Elapsed: 00:00:11.28`

But for 24 it is not that simple ðŸ™‚ at least for human !

Ok, in SQL I am using a plsql function to evaluate expression

```CREATE OR REPLACE FUNCTION lsc_eval (expr VARCHAR2) RETURN NUMBER IS x NUMBER; BEGIN EXECUTE IMMEDIATE 'begin :x := ' || expr || ';end;' USING OUT x; RETURN x; EXCEPTION WHEN OTHERS THEN RETURN NULL; END; / ```

I will post the rest of the code as a comment later ðŸ˜‰

## By Laurent Schneider

Oracle Certified Master

## 20 replies on “le compte est bon”

Bob Carltonsays:

(1^3)*6*4=24

only +-*/

Centinulsays:

If every number should be used only once and every operator can be reused, the only thing you need to find it – is RPN calculator. There are some interesting approaches on the net regarding the latter, but with very simple brute force attempt – it seems, you have only 56 positive results for your puzzle and 24 is not among of them… (of course, if my logic is not flawed ;-))

```/* Formatted on 2009/08/20 09:34 (Formatter Plus v4.8.8) */ WITH t AS (SELECT 1 n FROM DUAL UNION ALL SELECT 3 FROM DUAL UNION ALL SELECT 4 FROM DUAL UNION ALL SELECT 6 FROM DUAL), o AS (SELECT ' + ' op FROM DUAL UNION ALL SELECT ' - ' FROM DUAL UNION ALL SELECT ' * ' FROM DUAL UNION ALL SELECT ' / ' FROM DUAL) SELECT DISTINCT RESULT FROM (SELECT t1.n n1, o1.op op1, t2.n n2, o2.op op2, t3.n n3, o3.op op3, t4.n n4, lsc_eval ( lsc_eval ( lsc_eval ( t1.n || o1.op || t2.n ) || o2.op || t3.n ) || o3.op || t4.n ) RESULT, '((' || t1.n || o1.op || t2.n || ')' || o2.op || t3.n || ')' || o3.op || t4.n expr FROM t t1, t t2, t t3, t t4, o o1, o o2, o o3 WHERE t1.n <> t2.n AND t1.n <> t3.n AND t2.n <> t3.n AND t1.n <> t4.n AND t2.n <> t4.n AND t3.n <> t4.n UNION ALL SELECT t1.n n1, o1.op op1, t2.n n2, o2.op op2, t3.n n3, o3.op op3, t4.n n4, lsc_eval ( lsc_eval (t1.n || o1.op || t2.n) || o2.op || lsc_eval (t3.n || o3.op || t4.n) ) RESULT, '(' || t1.n || o1.op || t2.n || ')' || o2.op || '(' || t3.n || o3.op || t4.n || ')' FROM t t1, t t2, t t3, t t4, o o1, o o2, o o3 WHERE t1.n <> t2.n AND t1.n <> t3.n AND t2.n <> t3.n AND t1.n <> t4.n AND t2.n <> t4.n AND t3.n <> t4.n) WHERE MOD (RESULT, 1) = 0 AND RESULT > 0 ORDER BY RESULT```

[laurent schneider] indeed you miss 24 ðŸ™‚

each number is used only once, as I shown the 49 example

maxim approach is a good starting point… keep trying

```WITH lp AS (SELECT '' l FROM DUAL UNION ALL SELECT '(' FROM DUAL UNION ALL SELECT '((' FROM DUAL), digit AS (SELECT 1 n FROM DUAL UNION ALL SELECT 3 n FROM DUAL UNION ALL SELECT 4 n FROM DUAL UNION ALL SELECT 6 n FROM DUAL), rp AS (SELECT '' r FROM DUAL UNION ALL SELECT ')' FROM DUAL UNION ALL SELECT '))' FROM DUAL), op AS (SELECT '+' o FROM DUAL UNION ALL SELECT '-' FROM DUAL UNION ALL SELECT '*' FROM DUAL UNION ALL SELECT '/' FROM DUAL) SELECT l1.l || d1.n || o1.o || l2.l || d2.n || r2.r || o2.o || l3.l || d3.n || r3.r || o3.o || d4.n || r4.r result FROM lp l1, digit d1, op o1, lp l2, digit d2, rp r2, op o2, lp l3, digit d3, rp r3, op o3, digit d4, rp r4 WHERE 1 = 1 AND (l2.l IS NULL OR r2.r IS NULL) AND (l3.l IS NULL OR r3.r IS NULL) AND ( r2.r IS NOT NULL OR l3.l IS NOT NULL OR (LENGTH (NVL (l2.l, 0)) < 2 OR LENGTH (NVL (r3.r, 0)) < 2) ) AND ( r2.r IS NOT NULL OR l3.l IS NOT NULL OR r3.r IS NOT NULL OR (LENGTH (NVL (l2.l, 0)) < 2 OR LENGTH (NVL (r4.r, 0)) < 2) ) AND ( r2.r IS NOT NULL OR l3.l IS NOT NULL OR l2.l IS NOT NULL OR (LENGTH (NVL (l1.l, 0)) < 2 OR LENGTH (NVL (r3.r, 0)) < 2) ) AND ( l2.l IS NOT NULL OR (LENGTH (NVL (l1.l, 0)) < 2 OR LENGTH (NVL (r2.r, 0)) < 2) ) AND ( r3.r IS NOT NULL OR (LENGTH (NVL (l3.l, 0)) < 2 OR LENGTH (NVL (r4.r, 0)) < 2) ) AND ( l2.l IS NOT NULL OR r2.r IS NOT NULL OR l3.l IS NOT NULL OR r3.r IS NOT NULL OR l1.l IS NULL OR r4.r IS NULL ) AND LENGTH (l1.l || l2.l || l3.l) = 2 AND LENGTH (r2.r || r3.r || r4.r) = 2 AND (l1.l IS NULL OR r2.r IS NOT NULL OR r3.r IS NOT NULL) AND (r4.r IS NULL OR l2.l IS NOT NULL OR l3.l IS NOT NULL) AND LENGTH (l1.l || r4.r) <= 2 AND NVL (LENGTH (l1.l), 0) + NVL (LENGTH (l2.l), 0) >= NVL (LENGTH (r2.r), 0) AND NVL (LENGTH (l1.l), 0) + NVL (LENGTH (l2.l), 0) + NVL (LENGTH (l3.l), 0) >= NVL (LENGTH (r2.r), 0) + NVL (LENGTH (r3.r), 0) AND NVL (LENGTH (l1.l), 0) + NVL (LENGTH (l2.l), 0) + NVL (LENGTH (l3.l), 0) = NVL (LENGTH (r2.r), 0) + NVL (LENGTH (r3.r), 0) + NVL (LENGTH (r4.r), 0) AND d1.n NOT IN (d2.n, d3.n, d4.n) AND d2.n NOT IN (d3.n, d4.n) AND d3.n NOT IN (d4.n) AND NVL (LENGTH (r2.r), 0) <= 1 AND NVL (LENGTH (r3.r), 0) <= 2 AND NVL (LENGTH (l2.l), 0) <= 2 AND NVL (LENGTH (l3.l), 0) <= 1 AND (d1.n```

``` most of the code is to avoid solution like (1)+(1) ((1+1)) and other meaningless duplicates ðŸ™‚ runs in one second now ```
``` By Post Author ```
``` ```
``` Laurent Schneidersays: August 20, 2009 at 15:31 an untuned version would be WITH lp AS (SELECT column_value l FROM table(sys.odcivarchar2list('','(','(('))), digit AS (SELECT column_value n FROM table(sys.odcinumberlist(1,3,4,6))), rp AS (SELECT replace(l,'(',')') r FROM lp), op AS (SELECT column_value o FROM table(sys.odcivarchar2list('+','-','*','/'))) SELECT * FROM lp l1, digit d1, op o1, lp l2, digit d2, rp r2, op o2, lp l3, digit d3, rp r3, op o3, digit d4, rp r4 WHERE d1.n NOT IN (d2.n, d3.n, d4.n) AND d2.n NOT IN (d3.n, d4.n) AND d3.n NOT IN (d4.n) AND lsc_eval ( l1.l|| d1.n|| o1.o|| l2.l|| d2.n|| r2.r|| o2.o|| l3.l|| d3.n|| r3.r|| o3.o|| d4.n|| r4.r) = 24; but it will get a lot of duplicates and takes ages By Post Author le compte est bon | Oraclesays: August 21, 2009 at 05:31 […] Link to the original site Tags: Amp, Bex, Expression, Nbsp Nbsp Nbsp Nbsp Nbsp, ORACLE BLOGS, Quiz, Rhine, Rhone Glacier, Saint Leonard, Salt Mines, Sql, Switzerland, Underground Lake, Vacations, Varchar2, Waterfalls […] Sokratessays: August 23, 2009 at 11:50 Laurent, nice fun indeed ! I like the idea of your lsc_eval function and I remember a thread on the dizwell.com forum where I used the same function to solve a similar question. However, we all know, that you should probably not use it due to sql injection and so on, so here is a solution of your question using polish notation http://en.wikipedia.org/wiki/Polish_notation and not inflating or even poisoning the shared pool : create or replace function ev(xpr in varchar2) return number is i int; op1 varchar2(32); op1n number; op2 varchar2(32); op2n number; op varchar2(1); n varchar2(1); nco int; nnco int; begin op := substr(xpr, 1, 1); if op not in (‘+’, ‘-‘, ‘*’, ‘/’) then begin return to_number(xpr); exception when others then null; end; end if; nco := 0; nnco := 0; for i in 2..length(xpr) loop n := substr(xpr, i, 1); op1 := concat(op1, n); if n between ‘0’ and ‘9’ then nco := nco + 1; else nnco := nnco + 1; end if; if nco = nnco + 1 then op1n := ev(op1); op2n := ev(substr(xpr, i + 1)); return case op when ‘+’ then op1n + op2n when ‘-‘ then op1n – op2n when ‘*’ then op1n * op2n when ‘/’ then op1n / op2n end; end if; end loop; exception when others then return null; end ev; / with data as ( select decode( level, 1, ‘1+’, 2, ‘3-‘, 3, ‘4*’, 4, ‘6/’ ) d from dual connect by level <= 4 ), n as ( select substr(d, 1, 1) n from data ), o as ( select substr(d, 2, 1) o from data ), ps_ as ( select o1.o || n1.n || o2.o || n2.n || o3.o || n3.n || n4.n xpr1, o1.o || o2.o || n1.n || o3.o || n2.n || n3.n || n4.n xpr2, o1.o || n1.n || o2.o || o3.o || n2.n || n3.n || n4.n xpr3, o1.o || o2.o || o3.o || n1.n || n2.n || n3.n || n4.n xpr4 from o o1, o o2, o o3, n n1, n n2, n n3, n n4 where n1.n not in (n2.n, n3.n, n4.n) and n2.n not in (n3.n, n4.n) and n3.n not in (n4.n) ), ps as ( select xpr1 xpr from ps_ union all select xpr2 from ps_ union all select xpr3 from ps_ union all select xpr4 from ps_ ) select * from ps where ev( xpr ) = &n / Regards Matthias Rogel Sokratessays: August 23, 2009 at 12:19 small correction (forgot xpr5) with data as ( select decode( level, 1, ‘1+’, 2, ‘3-‘, 3, ‘4*’, 4, ‘6/’ ) d from dual connect by level <= 4 ), n as ( select substr(d, 1, 1) n from data ), o as ( select substr(d, 2, 1) o from data ), ps_ as ( select o1.o || n1.n || o2.o || n2.n || o3.o || n3.n || n4.n xpr1, o1.o || o2.o || n1.n || o3.o || n2.n || n3.n || n4.n xpr2, o1.o || n1.n || o2.o || o3.o || n2.n || n3.n || n4.n xpr3, o1.o || o2.o || o3.o || n1.n || n2.n || n3.n || n4.n xpr4, o1.o || o2.o || n1.n || n2.n || o3.o || n3.n || n4.n xpr5 from o o1, o o2, o o3, n n1, n n2, n n3, n n4 where n1.n not in (n2.n, n3.n, n4.n) and n2.n not in (n3.n, n4.n) and n3.n not in (n4.n) ), ps as ( select xpr1 xpr from ps_ union all select xpr2 from ps_ union all select xpr3 from ps_ union all select xpr4 from ps_ union all select xpr5 from ps_ ) select * from ps where round(ev( xpr) , 4 ) = &n / TSQL Challenge #12 Using Date Functions And Recursive CTE, Laurent Schneider Fun Stuff at Waldar’s SQLing and Datawarehousing Placesays: August 25, 2009 at 03:05 […] would just like to add a link to Laurent Schneider last entrie, which is a fun game […] Bob Carltonsays: August 25, 2009 at 05:37 (1+3)/(1/6)=24 Bob Carltonsays: August 25, 2009 at 05:40 ooops. forgot the 4. delete if you want. Laurent Schneidersays: August 25, 2009 at 15:54 we do not need dynamic sql actually. dynamic sql is slow and evil ðŸ™‚ /* Formatted on 2009/08/25 15:53 (Formatter Plus v4.8.8) */ WITH t AS (SELECT 1 x FROM DUAL UNION ALL SELECT 3 FROM DUAL UNION ALL SELECT 4 FROM DUAL UNION ALL SELECT 6 FROM DUAL), o AS (SELECT '+' o FROM DUAL UNION ALL SELECT '*' FROM DUAL UNION ALL SELECT '-' FROM DUAL UNION ALL SELECT '/' FROM DUAL), t1 AS (SELECT x x1 FROM t), t2 AS (SELECT x1, o o1, x x2, DECODE (o, '+', x1 + x, '*', x1 * x, '/', x1 / NULLIF (x, 0), '-', x1 - x ) r1 FROM t1, t, o WHERE x1 != x), t3 AS (SELECT 1 id1, x1, o1, x2, o o2, x x3, r1, DECODE (o, '+', r1 + x, '*', r1 * x, '/', r1 / NULLIF (x, 0), '-', r1 - x ) r2 FROM t2, t, o WHERE x NOT IN (x1, x2) UNION ALL SELECT 2 id1, x1, o1, x2, o o2, x x3, r1, DECODE (o, '+', x + r1, '*', x * r1, '/', x / NULLIF (r1, 0), '-', x - r1 ) r2 FROM t2, t, o WHERE x NOT IN (x1, x2)), t4 AS (SELECT id1, 1 id2, x1, o1, x2, o2, x3, o o3, x x4, r1, r2, DECODE (o, '+', r2 + x, '*', r2 * x, '/', r2 / NULLIF (x, 0), '-', r2 - x ) r3 FROM t3, t, o WHERE x NOT IN (x1, x2, x3) UNION ALL SELECT id1, 2 id2, x1, o1, x2, o2, x3, o o3, x x4, r1, r2, DECODE (o, '+', x + r2, '*', x * r2, '/', x / NULLIF (r2, 0), '-', x - r2 ) r3 FROM t3, t, o WHERE x NOT IN (x1, x2, x3) UNION ALL SELECT NULL, 3 id2, t21.x1, t21.o1, t21.x2, o o2, t22.x1 x3, t22.o1 o3, t22.x2 x4, t21.r1 r1, t22.r1 r2, DECODE (o, '+', t21.r1 + t22.r1, '*', t21.r1 * t22.r1, '/', t21.r1 / NULLIF (t22.r1, 0), '-', t21.r1 - t22.r1 ) r3 FROM t2 t21, t2 t22, o WHERE t21.x1 NOT IN (t22.x1, t22.x2) AND t21.x2 NOT IN (t22.x1, t22.x2)) SELECT DECODE (id2, 1, '(' || DECODE (id1, 1, '(' || x1 || o1 || x2 || ')' || o2 || x3, 2, x3 || o2 || '(' || x1 || o1 || x2 || ')' ) || ')' || o3 || x4, 2, x4 || o3 || '(' || DECODE (id1, 1, '(' || x1 || o1 || x2 || ')' || o2 || x3, 2, x3 || o2 || '(' || x1 || o1 || x2 || ')' ) || ')', 3, '(' || x1 || o1 || x2 || ')' || o2 || '(' || x3 || o3 || x4 || ')' ) expr, r3 FROM t4 WHERE ROUND (r3, 6) = 24; Elapsed: 00:00:00.00 :):) By Post Author Waldarsays: August 26, 2009 at 10:35 There is actually a small bug in your last code Laurent ðŸ˜‰ Waldarsays: August 26, 2009 at 10:45 Nine line from the end, you should transpose o1 and o2 : 2, x3 || o1 || ‘(‘ || x1 || o2 || x2 || ‘)’ –> 2, x3 || o2 || ‘(‘ || x1 || o1 || x2 || ‘)’ Laurent Schneidersays: August 26, 2009 at 12:14 thanks, I corrected this ðŸ™‚ By Post Author Laurent Schneidersays: August 26, 2009 at 12:27 it could be discussed if -18 has more than one solution with 1,3,4,6, depending if minus is restricted to his operator role By Post Author newkidsays: September 14, 2009 at 23:12 Here’s mine, I did it one year ago: create table t24 (n number); INSERT INTO t24 VALUES (1); INSERT INTO t24 VALUES (6); INSERT INTO t24 VALUES (3); INSERT INTO t24 VALUES (4); create table o24 (o varchar2 (1)); INSERT INTO o24 VALUES (‘+’); INSERT INTO o24 VALUES (‘-‘); INSERT INTO o24 VALUES (‘*’); INSERT INTO o24 VALUES (‘/’); COMMIT; WITH vw_tmp AS ( SELECT DECODE(o1.o,’+’,a.n+b.n ,’-‘,a.n-b.n ,’*’,a.n*b.n ,’/’,DECODE(b.n,0,NULL,a.n/b.n) ) AS ab ,DECODE(o2.o,’+’,b.n+c.n ,’-‘,b.n-c.n ,’*’,b.n*c.n ,’/’,DECODE(c.n,0,NULL,b.n/c.n) ) AS bc ,DECODE(o3.o,’+’,c.n+d.n ,’-‘,c.n-d.n ,’*’,c.n*d.n ,’/’,DECODE(d.n,0,NULL,c.n/d.n) ) AS cd ,a.n as a ,o1.o as o1 ,b.n as b ,o2.o as o2 ,c.n as c ,o3.o as o3 ,d.n as d from t24 a, t24 b,t24 c,t24 d,o24 o1,o24 o2,o24 o3 WHERE a.rowid NOT IN (b.rowid,c.rowid,d.rowid) AND b.rowid NOT IN (c.rowid,d.rowid) AND c.rowidd.rowid ) ,vw_tmp2 AS ( SELECT vw_tmp.* ,DECODE( o2,’+’,ab+c ,’-‘,ab-c ,’*’,ab*c ,’/’,DECODE(c,0,NULL,ab/c) ) AS ab_c ,DECODE( o1,’+’,a+bc ,’-‘,a-bc ,’*’,a*bc ,’/’,DECODE(bc,0,NULL,a/bc) ) AS a_bc ,DECODE( o3,’+’,bc+d ,’-‘,bc-d ,’*’,bc*d ,’/’,DECODE(d,0,NULL,bc/d) ) AS bc_d ,DECODE( o2,’+’,b+cd ,’-‘,b-cd ,’*’,b*cd ,’/’,DECODE(cd,0,NULL,b/cd) ) AS b_cd FROM vw_tmp ) ,vw_tmp3 AS ( SELECT ‘((‘||a||o1||b||’)’||o2||c||’)’||o3||d as formula ,DECODE( o3,’+’,ab_c+d ,’-‘,ab_c-d ,’*’,ab_c*d ,’/’,DECODE(d,0,NULL,ab_c/d) ) AS result FROM vw_tmp2 UNION ALL SELECT ‘(‘||a||o1||'(‘||b||o2||c||’))’||o3||d as formula ,DECODE( o3,’+’,a_bc+d ,’-‘,a_bc-d ,’*’,a_bc*d ,’/’,DECODE(d,0,NULL,a_bc/d) ) AS result FROM vw_tmp2 UNION ALL SELECT ‘(‘||a||o1||b||’)’||o2||'(‘||c||o3||d||’)’ as formula ,DECODE( o2,’+’,ab+cd ,’-‘,ab-cd ,’*’,ab*cd ,’/’,DECODE(cd,0,NULL,ab/cd) ) AS result FROM vw_tmp2 UNION ALL SELECT a||o1||'((‘||b||o2||c||’)’||o3||d||’)’ as formula ,DECODE( o1,’+’,a+bc_d ,’-‘,a-bc_d ,’*’,a*bc_d ,’/’,DECODE(bc_d,0,NULL,a/bc_d) ) AS result FROM vw_tmp2 UNION ALL SELECT a||o1||'(‘||b||o2||'(‘||c||o3||d||’))’ as formula ,DECODE( o1,’+’,a+b_cd ,’-‘,a-b_cd ,’*’,a*b_cd ,’/’,DECODE(b_cd,0,NULL,a/b_cd) ) AS result FROM vw_tmp2 ) SELECT formula,result FROM vw_tmp3 WHERE result=24; Laurent Schneidersays: September 17, 2009 at 12:34 very nice ðŸ™‚ By Post Author ```
``` ```
``` Leave a Reply Your email address will not be published.Comment Name Email Website ```
``` ```
``` ```