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 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #297662 is a reply to message #297660] Sat, 02 February 2008 03:25 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It seems that today is the ORDER BY day ...
SELECT col 
FROM TEST 
ORDER BY TO_NUMBER(REGEXP_REPLACE(col, '[[:graph:]][[:alpha:]]', '')),     
         REGEXP_REPLACE(col, '[[:digit:]]', '')
         NULLS FIRST;
Re: Order by result using instr [message #297681 is a reply to message #297662] Sat, 02 February 2008 07:15 Go to previous messageGo to next message
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 #297715 is a reply to message #297629] Sat, 02 February 2008 19:59 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Sorry to pile on another option:

SQL> select * from t1
  2     order by to_number(replace(a1,  translate(a1, '^0123456789 ', '^'))),
  3     translate(a1, '^-0123456789 ', '^') nulls first;

A1
--------------------
1
3
3-A
3-B
6
7
10
17-B
34
43

10 rows selected.

Re: Order by result using instr [message #297729 is a reply to message #297715] Sun, 03 February 2008 01:47 Go to previous message
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. Smile
Always nice to have a single thread with multiple options in it for some standard problem.
Previous Topic: Query Help
Next Topic: UTL_FILE error
Goto Forum:
  


Current Time: Sun Feb 09 10:00:09 CST 2025