1 2 Previous Next 29 Replies Latest reply: Jan 24, 2013 1:33 PM by AdamMartin RSS

    Need the best way to solve this problem

    Manik
      Gurus,

      I am sure I will learn something with this question...

      I am struck up in some issue. (This is not an project related question, instead its my own question just thinking how to solve) (My oracle version Oracle 11g R2)

      If I provide string like:

      1124567801030904 --- (numbers random order)

      I need output as

      1234567891040100 --- (numbers ordered observe: 1,2,3,4,5,6,7,8,9,10,40,100..)

      Just for the sake of understanding: I mean how to order it internally inside the string based on numbers.

      Its like
      First all digts 1,2,3,4,5,6,7,8,9
      next comes the format of 10,11,12..99
      next comes the format of 100,101,....999
      next comes the format of 1000,1001,.....


      Small suggestion: Please post different solutions so that I can learn all possible solutions to this problem.

      Best answer would be appreciated and rewarded!!!!!

      Thanks in advance!!!!!

      Cheers,
      Manik.
        • 1. Re: Need the best way to solve this problem
          NSK2KSN
          From the string 1124567801030904 how can we decide that what is 40 (or) 10 (or) 100?

          1234567891040100
          • 2. Re: Need the best way to solve this problem
            Manik
            Edited my question for clarity.

            Cheers,
            Manik.
            • 3. Re: Need the best way to solve this problem
              jeneesh
              Manik wrote:

              If I provide string like:

              1124567801030904 --- (numbers random order)

              I need output as

              1234567891040100 --- (numbers ordered observe: 1,2,3,4,5,6,7,8,9,10,40,100..)
              How are you getting 40 in the output?

              And your first 3 digits are 112. Is this not 1 and 12?

              How are you making it 1 and 2?

              And, this Does not look like a scenario which will come across in any practical project.. :)
              • 4. Re: Need the best way to solve this problem
                NSK2KSN
                Manik, I have got clarity on your required output, but am still trying to understand your input requirement?


                1124567801030904

                ok based on your input string

                after sorting 1 2 3 4 5 6 7 8 9 10 till here,

                remaining numbers are 04001

                so this numbers should become like this

                40 100

                is this correct??????
                • 5. Re: Need the best way to solve this problem
                  Manik
                  Right you got that.

                  Cheers,
                  Manik.
                  • 6. Re: Need the best way to solve this problem
                    Manik
                    jeneesh wrote:
                    Manik wrote:

                    If I provide string like:

                    1124567801030904 --- (numbers random order)

                    I need output as

                    1234567891040100 --- (numbers ordered observe: 1,2,3,4,5,6,7,8,9,10,40,100..)
                    How are you getting 40 in the output?

                    And your first 3 digits are 112. Is this not 1 and 12?

                    How are you making it 1 and 2?

                    And, this Does not look like a scenario which will come across in any practical project.. :)
                    Jeneesh,

                    Actually the string is jumbled in its internal order.
                    Did you get my question there..?

                    1 and 12 can also be considered, but the priority is 1,2,... and then 12.... got it?

                    Cheers,
                    Manik.
                    • 7. Re: Need the best way to solve this problem
                      user346369
                      This might work:

                      <font color="#00007F"><pre>Declare
                      Procedure msg(m varchar2) is begin
                      Dbms_output.put_line(m);
                      end;
                      ---
                      Procedure try_this(txt0 varchar2) is
                      txt1 varchar2(30) := substr(txt0,1,30);
                      txt2 Varchar2(30) := substr(txt0,1,30);
                      result varchar2(100);
                      t1 pls_integer := 0;
                      comma varchar2(1);
                      --
                      Procedure Get_num(t2 pls_integer) is
                      txt3 varchar2(30) := txt2;
                      nx varchar2(15) := to_char(t2);
                      digit varchar2(1);
                      p pls_integer;
                      Begin
                      for i in 1..length(nx) loop
                      digit := substr(nx,i,1);
                      p := instr(txt3,digit);
                      if p = 0 then
                      return;
                      end if;
                      txt3 := substr(txt3,1,p-1)||substr(txt3,p+1);
                      end loop;
                      If txt3 is not null
                      and to_number(txt3) < t2 then
                      --msg('reject '||t2||': '||txt3);
                      Return;
                      End if;
                      result := result || comma || nx;
                      txt2 := txt3;
                      comma := ',';
                      --msg(nx||', '||result||'. '||txt2);
                      End get_num;
                      ---
                      Begin
                      --msg('    '||txt2);
                      Loop
                      Exit when txt2 is null
                      or Length(to_char(t1))>Length(txt2);
                      t1 := t1 + 1;
                      Get_num(t1);
                      End Loop;
                      If txt2 is not null then
                      result := result ||' | '||txt2;
                      End if;
                      msg(txt1||'==>'||result||chr(10));
                      End try_this;
                      ---
                      Begin
                      try_this('1124567801030904');
                      try_this('1234567891040100');
                      try_this('1234567891040303935256');
                      End;
                      /</pre></font>

                      Here's the output:

                      <font color="#007F00"><pre>1124567801030904==>1,2,3,4,5,6,7,8,9,10,40,100

                      1234567891040100==>1,2,3,4,5,6,7,8,9,10,40,100

                      1234567891040303935256==>1,2,3,4,5,6,7,8,9,10,20,30,33,45,569</pre></font>

                      You can uncomment the msg() commands to watch it work.
                      • 8. Re: Need the best way to solve this problem
                        Manik
                        Steve,

                        First of all Thanks a ton :)

                        Great job! PL/SQL is one of solving this. Definetely agreed.

                        I will test your solution and will keep this thread open till I get the solution in plain SQL (if at all possible)...

                        Cheers,
                        Manik.
                        • 9. Re: Need the best way to solve this problem
                          Nicosa-Oracle
                          Hi,
                          Manik wrote:
                          1 and 12 can also be considered, but the priority is 1,2,... and then 12.... got it?
                          Well according to that, you'll never have "12" (twelve) in your string, as is will always be interpreted as "1,2".

                          By curiosity, how did you come to this question ?
                          • 10. Re: Need the best way to solve this problem
                            Manik
                            Just like that.. I still stick onto my point.. its not a requirement by any means.. just a thought .. trying to see how people look into it.

                            :)

                            Cheers,
                            Manik.
                            • 11. Re: Need the best way to solve this problem
                              user346369
                              Found a problem, had to tweak it a bit by sorting the digits high to low.

                              As for the comment, "you'll never have "12" (twelve) in your string", if you stick enough 1s and 2s in the string, you will. See the second example below.

                              <font color="#00007F"><pre>Declare
                              Procedure msg(m varchar2) is begin
                              Dbms_output.put_line(m);
                              end;
                              ---
                              Procedure try_this(txt0 varchar2) is
                              txt1 varchar2(30) := substr(txt0,1,30);
                              txt2 Varchar2(30) := substr(txt0,1,30);
                              result varchar2(100);
                              t1 pls_integer := 0;
                              comma varchar2(1);
                              --
                              Function sort(txt4 varchar2) return varchar2 is
                              txt5 varchar2(30) := substr(txt4,1,1);
                              c varchar2(1);
                              Begin
                              for j in 2..nvl(length(txt4),0) loop
                              c := substr(txt4,j,1);
                              for k in reverse 0..length(txt5) loop
                              If k = 0
                              or c <= substr(txt5,k,1) then
                              txt5 := substr(txt5,1,k)||c||substr(txt5,k+1);
                              exit;
                              end if;
                              end loop;
                              end loop;
                              Return txt5;
                              End;
                              --
                              Procedure Get_num(t2 pls_integer) is
                              txt3 varchar2(30) := txt2;
                              nx varchar2(15) := to_char(t2);
                              digit varchar2(1);
                              p pls_integer;
                              Begin
                              for i in 1..length(nx) loop
                              digit := substr(nx,i,1);
                              p := instr(txt3,digit);
                              if p = 0 then
                              return;
                              end if;
                              txt3 := substr(txt3,1,p-1)||substr(txt3,p+1);
                              end loop;
                              If txt3 is not null
                              and to_number(txt3) < t2 then
                              --msg('reject '||t2||': '||txt3);
                              Return;
                              End if;
                              result := result || comma || nx;
                              txt2 := txt3;
                              comma := ',';
                              --msg(result||'. '||txt2);
                              End get_num;
                              ---
                              Begin
                              --msg(' '||txt2);
                              txt2 := sort(txt2);
                              --msg(' '||txt2);
                              Loop
                              Exit when txt2 is null
                              or Length(to_char(t1))>Length(txt2);
                              t1 := t1 + 1;
                              Get_num(t1);
                              End Loop;
                              If txt2 is not null then
                              result := result ||' | '||txt2;
                              End if;
                              msg(txt1||'==>'||result||chr(10));
                              End try_this;
                              ---
                              Begin
                              try_this('1234567891040100');
                              try_this('12121234567891040100');
                              try_this('0309016780141245');
                              try_this('1257345678910403033526');
                              try_this('338952612536973456104030');
                              End;
                              /
                              </pre></font>

                              Here's the output:

                              <font color="#007F00"><pre>1234567891040100==>1,2,3,4,5,6,7,8,9,10,40,100

                              12121234567891040100==>1,2,3,4,5,6,7,8,9,10,11,12,20,400

                              0309016780141245==>1,2,3,4,5,6,7,8,9,10,40,100

                              1257345678910403033526==>1,2,3,4,5,6,7,8,9,10,20,30,33,45,567

                              338952612536973456104030==>1,2,3,4,5,6,7,8,9,10,20,30,33,34,55,669</pre></font>

                              Again, you can uncomment the msg() commands to watch it work.
                              • 12. Re: Need the best way to solve this problem
                                Nicosa-Oracle
                                Steve Cosner wrote:
                                As for the comment, "you'll never have "12" (twelve) in your string", if you stick enough 1s and 2s in the string, you will. See the second example below.
                                Does the output should have no "duplicates" ?
                                Because, that 2nd example puzzles me :
                                12121234567891040100==>1,2,3,4,5,6,7,8,9,10,11,12,20,400
                                The "121212" part, gives a "1,2" and a "12", but where do the last "12" from the input goes ? shouldn't it be considered 123 ? (and hence, remove the "3" from the output.

                                In my understanding, the example should actually give this, shouldn't it ?
                                12121234567891040100==> 1, 2, 4, 5, 6, 7, 8, 9, 10, 12, 40, 100, 123
                                The question is interesting, but the "question" is not totally defined as far as I understand. (To be more clear, I feel that one given input doesn't have a unique possible output).
                                • 13. Re: Need the best way to solve this problem
                                  APC
                                  Nicosa wrote:
                                  the "question" is not totally defined as far as I understand. (To be more clear, I feel that one given input doesn't have a unique possible output).
                                  The requirements are fuzzy because it's not a real problem, it's a game of "Code Golf".

                                  If an actual user came up to you and said, 'I have a random string of digits, can you convert them into a series of distinct integers summing to the smallest possible total while using all the digits?' you'd tell them, in the politest possible fashion, to do one.

                                  Undoubtedly there are some arcane areas of human endeavour (such as cryptography) which might have such problems but they wouldn't be using a much more suitable language than PL/SQL to solve them.

                                  Cheers, APC
                                  • 14. Re: Need the best way to solve this problem
                                    Nicosa-Oracle
                                    APC wrote:
                                    The requirements are fuzzy because it's not a real problem, it's a game of "Code Golf".
                                    I'm fine with the fact that it is a game, I pretty much love games +(and I love Golf)+.
                                    But in order to be "completable" +(does this word exist ?)+ it still needs well defined rules and goal (as Golf does).
                                    1 2 Previous Next