This discussion is archived
1 2 Previous Next 29 Replies Latest reply: Jan 24, 2013 11:33 AM by AdamMartin RSS

Need the best way to solve this problem

Manik Expert
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    Edited my question for clarity.

    Cheers,
    Manik.
  • 3. Re: Need the best way to solve this problem
    jeneesh Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    Right you got that.

    Cheers,
    Manik.
  • 6. Re: Need the best way to solve this problem
    Manik Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points