Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.5K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 401 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
SQL puzzle :)

598210
Member Posts: 305
I want to find all possible X up to the most highest number possbile with a single SQL.
X - Y = T + Z
where
X as a positive number,
Y is the reverse of X,
T is the sum of each number which X has
Z is the product of each number which X has
like in this example;
63 - 36 = 9 + 18
- Which is the most efficient way of producing for example numbers between 1 and 1000000000000, CONNECT BY from DUAL?
- Is there an alternative way to reverse a number other than undocumented REVERSE function to_number(reverse(to_char(anumber)) this way there will be three function calls for each number,
- What is the best way to break a number into pieces to sum or make product in SQL.
Thank you very much
X - Y = T + Z
where
X as a positive number,
Y is the reverse of X,
T is the sum of each number which X has
Z is the product of each number which X has
like in this example;
63 - 36 = 9 + 18
- Which is the most efficient way of producing for example numbers between 1 and 1000000000000, CONNECT BY from DUAL?
- Is there an alternative way to reverse a number other than undocumented REVERSE function to_number(reverse(to_char(anumber)) this way there will be three function calls for each number,
- What is the best way to break a number into pieces to sum or make product in SQL.
Thank you very much

Comments
-
Is calling user defined PL/SQL functions allowed or not?
-
wateenmooiedag the problem will be the performance most probably, but if it can not be handled within already SQL suppllied functionality then yes of course
By the way I will be using an 10.2 instance. -
SQL> create or replace function sum_prod(p_val in number) return number is
I think SQL experts can write it more elegantly.
2 l_val Varchar2(500) := p_val;
3 l_sum number := 0;
4 l_n1 number;
5 l_n2 number;
6 l_product number := 1;
7 l_first number;
8 begin
9 for i in 1..length(p_val)
10 loop
11 l_first := substr(l_val, 1, 1);
12 l_sum := l_sum + l_first;
13 l_product := l_product * l_first;
14 l_val := substr(l_val, 2);
15 end loop;
16 return(l_sum + l_product);
17 end;
18 /
Function created.
SQL> select rno from ( select rownum rno
2 from dual
3 connect by level <= 100)
4 where rno - reverse(to_char(rno)) = sum_prod(rno);
RNO
-----
63
SQL> select rno from ( select rownum rno
2 from dual
3 connect by level <= 1000)
4 where rno - reverse(to_char(rno)) = sum_prod(rno);
RNO
-----
63
726
SQL> select rno from ( select rownum rno
2 from dual
3 connect by level <= 10000)
4 where rno - reverse(to_char(rno)) = sum_prod(rno);
RNO
-----
63
726
8937 -
Citrus thank you for your time, but let me rephrase my concerns below, if I use 1000000000000 with this method even on a strong machine it takes half a day to finish.
- Which is the most efficient way of producing for example numbers between 1 and 1000000000000, CONNECT BY from DUAL?
- Is there an alternative way to reverse a number other than undocumented REVERSE function to_number(reverse(to_char(anumber)) this way there will be three function calls for each number,
- What is the best way to break a number into pieces to sum or make product in SQL. -
Hi,
The brute force won't help too much in your case.
You may want to exclude at least the number where the last digit is higher than the first.
And still, with such big number, I'm not sure SQL and PL/SQL will be very efficient.
Lastly, even if that takes half ad day to finish, I'm sure it's not a scheduled job, after running it once, it's finish.
Anyway, here below my first try on my laptop :SQL> create or replace type nb_obj as object (n1 number, n2 number, n3 number, n4 number);
Nicolas.
2 /
Type created.
Elapsed: 00:00:00.00
SQL>
SQL> create or replace type nb_list as table of nb_obj;
2 /
Type created.
Elapsed: 00:00:00.04
SQL>
SQL> create or replace function f_maths (p_max number) return nb_list pipelined is
2 x number:=0;
3 y number;
4 t number;
5 z number;
6
7 begin
8 loop
9 --X is a positive number
10 x := x+1;
11 if substr(x,-1,1) > substr(x,1,1)
12 then x := x+11-substr(x,-1,1);
13 end if;
14 exit when x > p_max;
15
16 y := null;
17 t := 0;
18 z := 1;
19 for j in 1..length(x) loop
20 --Y is the reverse of X
21 y:=y||to_char(substr(x,-j,1));
22 --T is the sum of each number which X has
23 t:=t+substr(x,-j,1);
24 --Z is the product of each number which X has
25 z:=z*substr(x,-j,1);
26 exit when y > substr(x,1,j);
27 end loop;
28 --X - Y = T + Z
29 if x - y = t + z then
30 pipe row (nb_obj(x,y,t,z));
31 end if;
32 end loop;
33 end;
34 /
Function created.
Elapsed: 00:00:00.04
SQL> show err
No errors.
SQL> select *
2 from table(f_maths(10000));
N1 N2 N3 N4
---------- ---------- ---------- ----------
63 36 9 18
726 627 15 84
8937 7398 27 1512
Elapsed: 00:00:00.06
SQL>
SQL> select *
2 from table(f_maths(100000));
N1 N2 N3 N4
---------- ---------- ---------- ----------
63 36 9 18
726 627 15 84
8937 7398 27 1512
Elapsed: 00:00:00.60
SQL>
SQL> select *
2 from table(f_maths(1000000));
N1 N2 N3 N4
---------- ---------- ---------- ----------
63 36 9 18
726 627 15 84
8937 7398 27 1512
Elapsed: 00:00:07.01
SQL>
SQL> select *
2 from table(f_maths(10000000));
N1 N2 N3 N4
---------- ---------- ---------- ----------
63 36 9 18
726 627 15 84
8937 7398 27 1512
Elapsed: 00:01:21.81
SQL>
SQL> select *
2 from table(f_maths(100000000));
N1 N2 N3 N4
---------- ---------- ---------- ----------
63 36 9 18
726 627 15 84
8937 7398 27 1512
Elapsed: 00:15:36.81 -
N. Gasparotto thank you for your responce.
Related to just using SQL functions I opened another thread here -
668861
But I guess since there are several function calls those solutions do not help both in performance and easy undertanding compared to a user defined pl/sql like yours. This was an interesting experience -
I replaced "SUM_PROD" function with wateenmooiedag's query:
2577576SQL> select rno from ( select rownum rno
2 from dual
3 connect by level <= 1000)
4 where rno - reverse(to_char(rno)) =
5 (select to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select '
6 ||regexp_replace(rno,'(.)','\1+') || '0'||'
7 s from dual')),'/ROWSET/ROW/S'))
8 + to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select '
9 ||regexp_replace(rno,'(.)','\1*') || '1'||'
10 p from dual')),'/ROWSET/ROW/P'))
11 from dual);
RNO
----------
63
726
SQL> -
YEah, XML and REGEXP are slow down. I'm pretty sure you can find some more rules to add in your function to exclude more and more numbers.
Nicolas. -
Just a new try, ~3 minutes less for hundred million...
SQL> drop type nb_list;
Nicolas.
Type dropped.
Elapsed: 00:00:00.11
SQL> create or replace type nb_obj as object (n1 number, n2 number, n3 number, n4 number);
2 /
Type created.
Elapsed: 00:00:00.01
SQL>
SQL> create or replace type nb_list as table of nb_obj;
2 /
Type created.
Elapsed: 00:00:00.15
SQL>
SQL> create or replace function f_maths (p_min number,p_max number) return nb_list pipelined is
2 x number:=p_min-1;
3 y number;
4 t number;
5 z number;
6 x1 number;
7 x2 number;
8 begin
9 loop
10 --X is a positive number
11 x := x+1;
12 x1 := substr(x,1,floor(length(x)/2));
13 x2 := substr(x,-floor(length(x)/2));
14 --dbms_output.put_line(x||' '||x1||' '||x2);
15 if x1 < x2 or length(x2) < length(x1)
16 then x := ceil(x/power(10,length(x2)))*power(10,length(x2));
17 end if;
18 exit when x > p_max;
19
20 y := null;
21 t := 0;
22 z := 1;
23 for j in 1..length(x) loop
24 --Y is the reverse of X
25 y:=y||to_char(substr(x,-j,1));
26 exit when y > substr(x,1,j);
27 --T is the sum of each number which X has
28 t:=t+substr(x,-j,1);
29 --Z is the product of each number which X has
30 z:=z*substr(x,-j,1);
31 end loop;
32 --X - Y = T + Z
33 if x - y = t + z then
34 pipe row (nb_obj(x,y,t,z));
35 end if;
36 end loop;
37 end;
38 /
Function created.
Elapsed: 00:00:00.04
SQL> show err
No errors.
SQL> select *
2 from table(f_maths(1,100000000));
N1 N2 N3 N4
---------- ---------- ---------- ----------
63 36 9 18
726 627 15 84
8937 7398 27 1512
Elapsed: 00:12:43.79
SQL> -
Probaly just interesting from an academical point of view. Performancewise it is just not acceptable:
SQL> select * from 2 xmltable('declare function local:reverse($a) 3 { 4 if (string-length($a) != 0) then 5 concat(substring($a,string-length($a),1), local:reverse(substring($a,1,string-length($a)-1))) 6 else () 7 }; (: eof :) 8 declare function local:sum($a) 9 { 10 if (string-length($a) != 0) then 11 xs:integer(substring($a,1,1)) + xs:integer(local:sum(substring($a,2))) 12 else (0) 13 }; (: eof :) 14 declare function local:prod($a) 15 { 16 if (string-length($a) != 0) then 17 xs:integer(substring($a,1,1)) * xs:integer(local:prod(substring($a,2))) 18 else (1) 19 }; (: eof :) 20 for $i in 1 to 10000 21 where $i - local:reverse(xs:string($i)) = local:sum(xs:string($i)) + local:prod(xs:string($i)) 22 return $i' columns x integer path '.') 23 / X ---------- 63 726 8937 Abgelaufen: 00:01:24.51
This discussion has been closed.