Home » SQL & PL/SQL » SQL & PL/SQL » Order by result using instr
Order by result using instr [message #297629] |
Fri, 01 February 2008 16:49  |
zepalways
Messages: 15 Registered: February 2008
|
Junior Member |
|
|
Hello there, I have a table that contains this data:
1, 10, 3, 3-A, 34, 7, 17-B, 6, 43, 3-B
I tried using this query:
select * from tablename order by instr('%[0-9]%-%[A,Z,0-9]%',columname);
and i get this result:
1
10
3
3-A
3-B
7
17-B
6
43
34
Is there anything to change to the select statement so that I get the output:
1
3
3-A
3-B
6
7
10
17-B
34
43
?
|
|
|
Re: Order by result using instr [message #297656 is a reply to message #297629] |
Sat, 02 February 2008 01:23   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | select * from tablename order by instr('%[0-9]%-%[A,Z,0-9]%',columname);
|
I don't know what you want to do with this but I bet you didn't read the documentation about INSTR function.
What is the question? Getting your result or using "instr" in "order by" clause?
By the way, post a test case, create table and insert statements, but before read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.
Regards
Michel
|
|
|
Re: Order by result using instr [message #297660 is a reply to message #297629] |
Sat, 02 February 2008 02:28   |
mshrkshl
Messages: 247 Registered: September 2006 Location: New Delhi
|
Senior Member |
|
|
SQL> select * from temp;
A
-----
1
10
3
3-A
34
7
17-B
6
43
3-B
10 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select a1||a2 from
2 ( select to_number(decode(substr(a,0,instr(a,'-')-1),
3 null,a,substr(a,0,instr(a,'-')-1))) as a1,
4 decode(substr(a,instr(A,'-')),(decode(substr(a,0,instr(a,'-')-1),
5 null,a,substr(a,0,instr(a,'-')-1))),null,substr(a,instr(A,'-'))) as a2
6 from temp
7* order by 1)
SQL> /
A1||A2
---------------------------------------------
1
3
3-A
3-B
6
7
10
17-B
34
43
10 rows selected.
|
|
|
|
Re: Order by result using instr [message #297681 is a reply to message #297662] |
Sat, 02 February 2008 07:15   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Another option:
create table faq (col_a varchar2(10))
/
insert into faq values('1');
insert into faq values('10');
insert into faq values('3');
insert into faq values('3-A');
insert into faq values('34');
insert into faq values('7');
insert into faq values('17-B');
insert into faq values('6');
insert into faq values('43');
insert into faq values('3-B');
select col_a
from (select col_a
, case when instr(col_a, '-') > 0
then 1 + length(col_a) - instr(col_a, '-')
else 0
end as own_postfix_length
, max(case when instr(col_a, '-') > 0
then 1 + length(col_a) - instr(col_a, '-')
else 0
end
) over () as max_postfix_length
from faq)
order by lpad(col_a||rpad(' ', max_postfix_length - own_postfix_length), 200)
/
Or a rewrite of mshrkshl's query:
with data as
( select col_a
, instr(col_a, '-') - 1 as token_position
from faq
)
select a1 || a2
from ( select to_number(case when token_position = -1
then col_a
else substr(col_a, 1, token_position)
end
) as a1
, case when token_position > 0
then substr(col_a, token_position + 1)
else ''
end as a2
from data
)
order by a1
, a2
|
|
|
|
Re: Order by result using instr [message #297729 is a reply to message #297715] |
Sun, 03 February 2008 01:47  |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Don't feel sorry, we can make this thread the mother of all sort-numbers-with-postfixes-threads. 
Always nice to have a single thread with multiple options in it for some standard problem.
|
|
|
Goto Forum:
Current Time: Sun Feb 09 10:00:09 CST 2025
|