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.28But 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 
(1^3)*6*4=24
only +-*/
What about (1/3)*(3/1)*6*4
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
)
[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
most of the code is to avoid solution like (1)+(1) ((1+1)) and other meaningless duplicates
runs in one second now
an untuned version would be
but it will get a lot of duplicates and takes ages
Pingback: le compte est bon | Oracle
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
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
/
Pingback: TSQL Challenge #12 Using Date Functions And Recursive CTE, Laurent Schneider Fun Stuff at Waldar’s SQLing and Datawarehousing Place
(1+3)/(1/6)=24
ooops. forgot the 4. delete if you want.
we do not need dynamic sql actually. dynamic sql is slow and evil
There is actually a small bug in your last code Laurent
Nine line from the end, you should transpose o1 and o2 :
2, x3 || o1 || ‘(‘ || x1 || o2 || x2 || ‘)’
–>
2, x3 || o2 || ‘(‘ || x1 || o1 || x2 || ‘)’
thanks, I corrected this
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
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;
very nice