1 2 3 Previous Next 61 Replies Latest reply on Aug 19, 2013 9:39 AM by Manik

# Trying to solve 6 Number 1 target android game using oracle (sql or pl/sql)

Gurus/mentors/Friends,

Today I had some space in my work time, so tried to replicate an android game called "6 numbers 1 target" in oracle.

All I need help from you all is to make it more efficient/robust and garnish it with a bit of awesomeness  (preferable in SQL ??? which I did not get )

Please note that if you are busy, ignore this question.

------------------------------------------------------------------------------------------------

Game goes like this:

-------------------------

computer gives you only 6 numbers ...

e.g. :  50,9,5,8,6,7

And using mathematical operators like '+','-','*','/'  we need to get a result like 292.  (this number 292 is also given by computer..)

All we need to find is the apt formula for getting 292 out of those 6 numbers (you may/may not use all of those numbers) by using those operators (you may use them without any restiction of number of times they are used and also decimals are truncated.).

So the solutions can be : 50*6-8+5/9/7     or   50*6-8  or  50*6-8+5/7/9 etc....   (multiple answers are possible, all boiling down giving 292)

I just tried to write it in this way, but its taking a lot of time as the levels go up. So I thought I would dice it in this forum for better solution.( or may be suggestions to improve my solution below)

------------------------------------

create table demo (str varchar2(100), val number);

-----------------------------------

DECLARE
l_str      VARCHAR2 (100);
l_result   NUMBER;
BEGIN
EXECUTE IMMEDIATE 'truncate table demo';

WHILE (TRUE) LOOP             ----------------------------------- I know this is evil, but this is game afterall and I needed iterations to go on until I derive at a solution.
BEGIN
FOR rec
IN (WITH operators AS
(  SELECT COLUMN_VALUE op
FROM TABLE (sys.odcivarchar2list ('+',
'-',
'*',
'/'))
ORDER BY DBMS_RANDOM.VALUE ()),
t AS
(    SELECT SUBSTR (SYS_CONNECT_BY_PATH (letter, ','), 2)
word
FROM (    SELECT LEVEL LVL,
REGEXP_SUBSTR (str,
'[^,]+',
1,
LEVEL)
LETTER
FROM (SELECT '7,8,5,50,9,6' str FROM DUAL) t
CONNECT BY LEVEL <=
REGEXP_COUNT (STR, ',') + 1)
WHERE LEVEL = 6
CONNECT BY NOCYCLE lvl != PRIOR lvl),
tt AS
(SELECT REGEXP_SUBSTR (word,
'[^,]*',
1,
1)
col1,
(SELECT val
FROM (  SELECT op val
FROM operators
ORDER BY DBMS_RANDOM.VALUE ())
WHERE ROWNUM = 1)
op1,
REGEXP_SUBSTR (word,
'[^,]*',
1,
3)
col2,
(SELECT val
FROM (  SELECT op val
FROM operators
ORDER BY DBMS_RANDOM.VALUE ())
WHERE ROWNUM = 1)
op2,
REGEXP_SUBSTR (word,
'[^,]*',
1,
5)
col3,
(SELECT val
FROM (  SELECT op val
FROM operators
ORDER BY DBMS_RANDOM.VALUE ())
WHERE ROWNUM = 1)
op3,
REGEXP_SUBSTR (word,
'[^,]*',
1,
7)
col4,
(SELECT val
FROM (  SELECT op val
FROM operators
ORDER BY DBMS_RANDOM.VALUE ())
WHERE ROWNUM = 1)
op4,
REGEXP_SUBSTR (word,
'[^,]*',
1,
9)
col5,
(SELECT val
FROM (  SELECT op val
FROM operators
ORDER BY DBMS_RANDOM.VALUE ())
WHERE ROWNUM = 1)
op5,
REGEXP_SUBSTR (word,
'[^,]*',
1,
11)
col6
FROM t)
SELECT    col1
|| op1
|| col2
|| op2
|| col3
|| op3
|| col4
|| op4
|| col5
|| op5
|| col6
formula
FROM tt
ORDER BY DBMS_RANDOM.VALUE ()) LOOP
EXECUTE IMMEDIATE 'begin :result := ' || rec.formula || '; end;'
USING OUT l_result;

l_str := rec.formula;

INSERT INTO demo
VALUES (rec.formula, TRUNC (l_result));
-------COMMIT;

END LOOP;
END;

IF (l_result = 292) THEN
EXIT;
END IF;
END LOOP;
END;
/

--------------------------------------------------------------------------------------------------

Thanks in advance!!!!  (even for those who opened this question )

Cheers,

Manik.

• ###### 1. Re: Trying to solve 6 Number 1 target android game using oracle (sql or pl/sql)

Ooo, I feel like I'm watching CountDown on Channel 4.... and Carol Vorderman is picking the numbers out....

Countdown (game show) - Wikipedia, the free encyclopedia

• ###### 2. Re: Trying to solve 6 Number 1 target android game using oracle (sql or pl/sql)

Warning: this is a lot of permutations and takes a long time to run to completion.

with numstr as (select '50,9,5,8,6,7' as numstr from dual)

,targ as (select 292 targ from dual)

-- end of input

,ops as (select '+' as op from dual union all

select '-' from dual union all

select '*' from dual union all

select '/' from dual)

,nums as (select rownum as nid, to_number(regexp_substr(numstr,'[^,]+',1,rownum)) as num

from numstr

connect by rownum <= 6)

,perms as (select distinct

regexp_replace(replace(sys_connect_by_path(to_char(num)||op,' '),' '),'.\$') as perm

from   nums cross join ops

connect by nocycle to_char(nid)||op != prior to_char(nid)||op

)

select perm

from perms cross join targ

,xmltable(replace(perm,'/',' div ')) x

where to_number(x.column_value) = targ.targ

/

It also doesn't take account of manipulating order of precedence, which would have to be done with all the permutations of putting brackets around the various pairs or sets of possible operations within each formula.

That would just extend the amount of time taken.

• ###### 4. Re: Trying to solve 6 Number 1 target android game using oracle (sql or pl/sql)

p.s. this could also be done with recursive subquery factoring... but fastest is the human brain which can recognise patterns etc. and eliminate obvious wrong answers quickly.

e.g. the quickest answer to that would be (50*6)-8, just by looking at it.

• ###### 5. Re: Trying to solve 6 Number 1 target android game using oracle (sql or pl/sql)

Ooo, I feel like I'm watching CountDown on Channel 4.... and Carol Vorderman is picking the numbers out....

Countdown (game show) - Wikipedia, the free encyclopedia

The original French version is very famous here too.

I've made a query once to solve the letter puzzle, based on Hunspell files.

• ###### 6. Re: Trying to solve 6 Number 1 target android game using oracle (sql or pl/sql)

Well, my query finally completed with all it's answers:

PERM
-----------------------
5*50+6*7
5*50+7*6
5*6*8+50+9-7
5*6*8+50-7+9
5*6*8+9+50-7
5*6*8+9-7+50
5*6*8-7+50+9
5*6*8-7+9+50
5*8*6+50+9-7
5*8*6+50-7+9
5*8*6+9+50-7
5*8*6+9-7+50
5*8*6-7+50+9
5*8*6-7+9+50
50*5+6*7
50*5+7*6
50*6+8-7-9
50*6+8-9-7
50*6-7+8-9
50*6-7-9+8
50*6-8
50*6-9+8-7
50*6-9-7+8
50+5*6*8+9-7
50+5*6*8-7+9
50+5*8*6+9-7
50+5*8*6-7+9
50+6*5*8+9-7
50+6*5*8-7+9
50+6*8*5+9-7
50+6*8*5-7+9
50+8*5*6+9-7
50+8*5*6-7+9
50+8*6*5+9-7
50+8*6*5-7+9
50+9+5*6*8-7
50+9+5*8*6-7
50+9+6*5*8-7
50+9+6*8*5-7
50+9+8*5*6-7
50+9+8*6*5-7
50+9-7+5*6*8
50+9-7+5*8*6
50+9-7+6*5*8
50+9-7+6*8*5
50+9-7+8*5*6
50+9-7+8*6*5
50-7+5*6*8+9
50-7+5*8*6+9
50-7+6*5*8+9
50-7+6*8*5+9
50-7+8*5*6+9
50-7+8*6*5+9
50-7+9+5*6*8
50-7+9+5*8*6
50-7+9+6*5*8
50-7+9+6*8*5
50-7+9+8*5*6
50-7+9+8*6*5
6*5*8+50+9-7
6*5*8+50-7+9
6*5*8+9+50-7
6*5*8+9-7+50
6*5*8-7+50+9
6*5*8-7+9+50
6*50+8-7-9
6*50+8-9-7
6*50-7+8-9
6*50-7-9+8
6*50-8
6*50-9+8-7
6*50-9-7+8
6*7+5*50
6*7+50*5
6*8*5+50+9-7
6*8*5+50-7+9
6*8*5+9+50-7
6*8*5+9-7+50
6*8*5-7+50+9
6*8*5-7+9+50
7*6+5*50
7*6+50*5
8*5*6+50+9-7
8*5*6+50-7+9
8*5*6+9+50-7
8*5*6+9-7+50
8*5*6-7+50+9
8*5*6-7+9+50
8*6*5+50+9-7
8*6*5+50-7+9
8*6*5+9+50-7
8*6*5+9-7+50
8*6*5-7+50+9
8*6*5-7+9+50
8+50*6-7-9
8+50*6-9-7
8+6*50-7-9
8+6*50-9-7
8-7+50*6-9
8-7+6*50-9
8-7-9+50*6
8-7-9+6*50
8-9+50*6-7
8-9+6*50-7
8-9-7+50*6
8-9-7+6*50
9+5*6*8+50-7
9+5*6*8-7+50
9+5*8*6+50-7
9+5*8*6-7+50
9+50+5*6*8-7
9+50+5*8*6-7
9+50+6*5*8-7
9+50+6*8*5-7
9+50+8*5*6-7
9+50+8*6*5-7
9+50-7+5*6*8
9+50-7+5*8*6
9+50-7+6*5*8
9+50-7+6*8*5
9+50-7+8*5*6
9+50-7+8*6*5
9+6*5*8+50-7
9+6*5*8-7+50
9+6*8*5+50-7
9+6*8*5-7+50
9+8*5*6+50-7
9+8*5*6-7+50
9+8*6*5+50-7
9+8*6*5-7+50
9-7+5*6*8+50
9-7+5*8*6+50
9-7+50+5*6*8
9-7+50+5*8*6
9-7+50+6*5*8
9-7+50+6*8*5
9-7+50+8*5*6
9-7+50+8*6*5
9-7+6*5*8+50
9-7+6*8*5+50
9-7+8*5*6+50
9-7+8*6*5+50

142 rows selected.

Now if anyone wants to do it with brackets to get all the other answers.... and perhaps optimize it to eliminate the similar formulae (i.e. where 6*8 is the same as 8*6 etc.) ... now that's a challenge hehe!

• ###### 7. Re: Trying to solve 6 Number 1 target android game using oracle (sql or pl/sql)

They should perhaps modify oxford dictionary.... synonym of genius should be changed   (genius = refer oracle forums (gurus grade especially) you will find lot of them )

I surely agree that human brain is the best to solve this.. +10 points for that.

Blu, I will wait for others to look into this problem as well.. as there might be people who think out of box may be.

But I really like your approach.. Thanks for making me think out of my box

Cheers,

Manik.

• ###### 8. Re: Trying to solve 6 Number 1 target android game using oracle (sql or pl/sql)

Cheers,

Manik,

• ###### 9. Re: Trying to solve 6 Number 1 target android game using oracle (sql or pl/sql)

Ok, a quick bit of optimization to remove duplication caused by + and *....

SQL> with numstr as (select '50,9,5,8,6,7' as numstr from dual)
2      ,targ as (select 292 targ from dual)
3  -- end of input
4      ,ops as (select '+' as op from dual union all
5               select '-' from dual union all
6               select '*' from dual union all
7               select '/' from dual)
8      ,nums as (select rownum as nid, to_number(regexp_substr(numstr,'[^,]+',1,rownum)) as num
9                from numstr
10                connect by rownum <= 6)
11      ,perms as (select distinct
12                        regexp_replace(replace(sys_connect_by_path(to_char(num)||op,' '),' '),'.\$') as perm
13                 from   nums cross join ops
14                 connect by nocycle to_char(nid)||op != prior to_char(nid)||op and ((prior num > num and op in ('+','*')) or op in ('-','/'))
15                )
16  select perm
17  from perms cross join targ
18      ,xmltable(replace(perm,'/',' div ')) x
19  where to_number(x.column_value) = targ.targ
20  /

PERM
----------------------------------------------------------------------------------------------------------------------------------------------------------
50*6+8-7-9
50*6+8-9-7
50*6-8
50*6-9-7+8
50+6*5*8-7+9
50+8*6*5+9-7
50+9+6*5*8-7
50+9+8*5*6-7
50+9+8*6*5-7
50+9-7+6*5*8
6*50-7+8-9
6*50-8
6*50-9+8-7
6*50-9-7+8
7*6+5*50
8*6*5+50-7+9
8*6*5+9-7+50
8+6*50-7-9
8+6*50-9-7
8-7+6*50-9
8-9-7+6*50
9+50-7+6*5*8
9+6*5*8-7+50
9+8*6*5+50-7

24 rows selected.

Just need to optimize further to remove those that are essentially the same through + and -... but maybe I'll leave that to someone else.

• ###### 10. Re: Trying to solve 6 Number 1 target android game using oracle (sql or pl/sql)

How much time does it take ?

• ###### 11. Re: Trying to solve 6 Number 1 target android game using oracle (sql or pl/sql)

How about just executing in parallel?

• ###### 12. Re: Trying to solve 6 Number 1 target android game using oracle (sql or pl/sql)

That optimized version took about 5 minutes I think (I wasn't keeping an eye on it)

• ###### 13. Re: Trying to solve 6 Number 1 target android game using oracle (sql or pl/sql)

I'm just considering if a recursive subquery factoring would be better.... hmmm...

• ###### 14. Re: Trying to solve 6 Number 1 target android game using oracle (sql or pl/sql)

............Now if anyone wants to do it with brackets to get all the other answers....

Add brackets will need further clarification on the rule(s),

(a+b)*c = a*c+b*c   (can c be used twice?)

But without brackets, all possible expressions have to be listed first and then dynamically executed (so no help with where clause to eliminate intermediate results)

For 6 numbers, it may not be too bad (consider less the max),

SQL> select (6*5*4*3*2*1)*(4*4*4*4*4) from dual ;

(6*5*4*3*2*1)*(4*4*4*4*4)

-------------------------

737280

1 2 3 Previous Next