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

# Need the best way to solve this problem

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!!!!!

Cheers,
Manik.
• ###### 1. Re: Need the best way to solve this problem
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
Currently Being Moderated
Edited my question for clarity.

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

Cheers,
Manik.
• ###### 6. Re: Need the best way to solve this problem
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
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
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
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
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
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
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
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
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