oracle table sorting in sql query- oracle 10g

[mdvreddy]

Hi,

data is as follows:

1 C234
2 c234a
3 axe tronze
4 c234z
5 zingx
6 b678
8 b678Z
9 b678t
10 d123z
11 d123t
12 d123

should be displayed:


8 b678Z
9 b678t
6 b678
4 c234z
2 c234a
1 C234
10 d123z
11 d123t
12 d123
3 axe tronze
5 zingx

first Four letters of second column should be ascending order and last 1 character of second column should be descending order.
if second column contains only text then the data should come in ascending order at end. it should be implemented as refcursor and should return in above format.

frequent insert and delete operations... is there any way to sort in above said order... please suggest me with correct query SQL statement.. thanks in advance.

[gandolf989]

you can use instr with substr to find the first space and then take the next for characters, make a column from this and then sort on that column.

[ebrian]

Wasn't certain if the letters in your output were typically mixed case or not. If the case is mixed, the first query should suffice. If the case is all lowercase to begin with, the last query should work.

Mixed case example:

SQL> select * from t1;

        A1 A2
---------- --------------------
         1 C234
         2 c234a
         3 axe tronze
         4 c234z
         5 zingx
         6 b678
         8 b678Z
         9 b678t
        10 d123z
        11 d123t
        12 d123

11 rows selected.


SQL> select * from t1
  2  order by regexp_substr(a2, '^[^0-9]+$') nulls first
  3     , lower(regexp_substr(a2, '^.{4}')) asc
  4     , lower(regexp_substr(a2, '[^0-9]$')) desc nulls last;

        A1 A2
---------- --------------------
         8 b678Z
         9 b678t
         6 b678
         4 c234z
         2 c234a
         1 C234
        10 d123z
        11 d123t
        12 d123
         3 axe tronze
         5 zingx

11 rows selected.

All lowercase example:

SQL> select * from t1;

        A1 A2
---------- --------------------
         1 c234
         2 c234a
         3 axe tronze
         4 c234z
         5 zingx
         6 b678
         8 b678z
         9 b678t
        10 d123z
        11 d123t
        12 d123

11 rows selected.


SQL> select * from t1
  2  order by regexp_substr(a2, '^[^0-9]+$') nulls first
  3     , regexp_substr(a2, '^.{4}') asc
  4     , regexp_substr(a2, '[^0-9]$') desc nulls last;

        A1 A2
---------- --------------------
         8 b678z
         9 b678t
         6 b678
         4 c234z
         2 c234a
         1 c234
        10 d123z
        11 d123t
        12 d123
         3 axe tronze
         5 zingx

11 rows selected.

[hrishy]

Hi

Ebrian can you please explian what your regular expressions are doing.

regards
Hrishy

[ebrian]

regexp_substr(a2, '^[^0-9]+$')
Is looking for strings that do NOT contain any numbers anywhere in the string.
The first ^ indicates the beginning of the string and the $ indicates the end of the string. Both are used to signify the entire string.
[^0-9] indicates NO characters in this class (ie. NO numbers).
+ indicates one or more of the preceding expression (ie. one or more numbers).


regexp_substr(a2, '^.{4}')
Is strictly looking at the first 4 characters.
^ indicates beginning of string.
The . indicates any character.
{4} is specifying 4 occurrences of the preceding expression. (ie. 4 of any characters)


regexp_substr(a2, '[^0-9]$')
Is extracting the last character as long as it's NOT a number.


Hope this helps.

[hrishy]

Hi

One word for your explanation Excellent.

regards
Hrishy

[mdvreddy]

great thanks, but still one more issue please.

i have below and coming now as below

Ona
Ona_a
Ona_c

but all these should come in below orader.

Ona_c
Ona_a
Ona

could it be done please?. give me query please! thanks

[gandolf989]

It looks like you need to add the word reverse to each part of the order by clause, or at least the last part.

[PAVB]

great thanks, but still one more issue please.

i have below and coming now as below

Ona
Ona_a
Ona_c

but all these should come in below orader.

Ona_c
Ona_a
Ona

could it be done please?. give me query please! thanks

Looking at your specs... if second column contains only text then the data should come in ascending order at end. I would say ebrian's first query does exactly what you have asked for.

Am I missing something?

[mdvreddy]

no, i tried with both query/methods, they are not coming in expected way..
could ebrian/you please help me out? thanks..