Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Chirayu.

Asked: March 30, 2014 - 12:30 pm UTC

Last updated: December 02, 2014 - 12:18 pm UTC

Version: 11G

Viewed 1000+ times

You Asked

Hi Tom,

I want to do an order by for a query. The result expected is as shown below.
I can achieve what i want using regular expression.
However, I want to know whether that can be achieved without using regular expression.

I want the order by in such a way that

1. we scan left to right . for example for values like '12 CK', '13 CK'
The order by should be '12 CK', '13 CK' as rest all being equal 12 comes before 13.

2. sort as per the ASCII values

for strings like , '12 CK','30', '7999', 'app'
If we scan from left to right, 12 comes first, then '30', '7999' and finally 'app'

I hope I am clear.

create table TEST_TB1(COL1 varchar2(240));

insert into TEST_TB1 values ('700000');
insert into TEST_TB1 values ('CH120CND');
insert into TEST_TB1 values ('CH120ABD');
insert into TEST_TB1 values ('2999');
insert into TEST_TB1 values ('30');
insert into TEST_TB1 values ('app');
insert into TEST_TB1 values ('band');
insert into TEST_TB1 values ('12 CK');
insert into test_tb1 values('13 CK');
insert into test_tb1 values('appl1');

RESULT EXPECTED
-------------------------
12 CK
13 CK
30
2999
700000
CH120CND
CH121ABD
app
appl1
band



Thanks

and Tom said...

as clear as mud, but I think you mean:

if the string begins with a number (ending with the end of string or a space), we would like to first order by the number portion - otherwise assign null) and sort by that using a numeric sort

then take anything after that number (or if the string does not begin with a number, take the entire string) and sort by that using string sorts.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select col1, oc1,
  2         case when oc1 is not null
  3              then substr( col1, instr(col1||' ',' ')+1)
  4              else col1
  5          end oc2
  6    from (select col1,
  7                 case when replace( translate( trim(substr( col1, 1, instr(col1||' ', ' '))),'0123456789','0000000000'), '0', '') is null
  8                      then to_number( substr( col1, 1, instr(col1||' ', ' ')) )
  9                  end oc1
 10             from t )
 11   order by oc1, oc2
 12  /

COL1              OC1 OC2
---------- ---------- ----------
12 CK              12 CK
13 CK              13 CK
30                 30
2999             2999
700000         700000
CH120ABD              CH120ABD
CH120CND              CH120CND
app                   app
appl1                 appl1
band                  band

10 rows selected.



this doesn't get your output exactly, but that is only because your example doesn't insert CH121ABD, but your output somehow magically retrieves it.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

compare with regexp

Chirayu Sutaria, April 02, 2014 - 12:23 pm UTC

Sorry for the typo CH121ABD.

The equivalent expression is

select * from t
order by regexp_replace (regexp_replace (col1, '(\d+)', lpad('\1=',
243, '0')), RPAD('0+(',243 ,LPAD(')\=',240 , '.')), '\1') ASC;

The value is padded to 243 characters to match with length of the column(it's hard coded so I do see an issue here if the length of the column changes)

Of the two possible solutions, which one is more efficient considering that the order by is for a view having transaction tables with millions of records.

Can there be a way without using select of select.

query

Igor, April 02, 2014 - 8:07 pm UTC 

Yes, this can be done without select of select

SQL> select * from TEST_TB1
  2  order by translate(substr(col1,1,1),'0123456789',lpad(' ',10,' ')),
  3  lpad('0',50-instr(translate(col1||'A',
  4  'QqWwEeRrTtYyUuIiOoPpAaSsDdFfGgHhJjKkLlZzXxCcVvBbNnMm~!@#$%^&*()_+ ',
  5  lpad('A',66,'A')),'A',1,1),'0')||col1;

COL1
------------------------------
12 CK
13 CK
30
2999
700000
CH120ABD
CH120CND
app
appl1
band

A reader, November 13, 2014 - 3:19 pm UTC

It's very simple.
Select emp_id,emp_no,name from emp
order by emp_id,emp_no

Any other way

Devarsh, December 02, 2014 - 2:56 am UTC

Hi Tom,
I don't want to use select in select as it's our form block based code which does not support(involves complete re-write).

The solution suggested by was

Yes, this can be done without select of select

select * from TEST_TB1
order by translate(substr(col1,1,1),'0123456789',lpad(' ',10,' ')),
lpad('0',50-instr(translate(col1||'A',
'QqWwEeRrTtYyUuIiOoPpAaSsDdFfGgHhJjKkLlZzXxCcVvBbNnMm~!@#$%^&*()_+ ',
lpad('A',66,'A')),'A',1,1),'0')||col1;

does this take care of a characterset which is other than English ?

Is there any other way to do it without using select on select.

The regexp_replace might have a bug , refer bug 20118338.

Thanks
Tom Kyte
December 02, 2014 - 12:18 pm UTC

I don't want to use select in select as it's our form block based code which
does not support(involves complete re-write).

great, a framework that doesn't support SQL!

it's been a long time since I've looked at this issue, but if you use my approach, I am only looking at NUMBERS and commas - no other characters. I'm not going to debug someone elses approach. You can just replace OC1 (unnest it from the inline view). I used the select of a select for readability. There is no technical reason it has to be a select of a select.

a) Understand what I'm doing
b) re-implement it without using select of a select

ops$tkyte%ORA11GR2> select col1, oc1,
  2         case when oc1 is not null
  3              then substr( col1, instr(col1||' ',' ')+1)
  4              else col1
  5          end oc2
  6    from (select col1,
  7                 case when replace( translate( trim(substr( col1, 1, 
instr(col1||' ', ' '))),'0123456789','0000000000'), '0', '') is null
  8                      then to_number( substr( col1, 1, instr(col1||' ', ' 
')) )
  9                  end oc1
 10             from t )
 11   order by oc1, oc2
 12  /

Thank you

Devarsh, December 03, 2014 - 2:43 am UTC

My bad, I didn't review the response properly.
Thanks for taking care to respond.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.