Skip to Main Content
  • Questions
  • Sorting -- sort a string like a number.

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: September 17, 2000 - 5:20 pm UTC

Last updated: November 09, 2018 - 2:20 am UTC

Version: version8.1.6

Viewed 100K+ times! This question is

You Asked

I have a alphanumeric column in the database.
It contains only numeric values.Is it possible to do
a numeric sort directly by a sql command like "order by <column>"

and Tom said...

Yes, if they are all numbers in the character field, you can:

select * from T order by to_number(column);


In fact, in Oracle8i, you could even create an index on to_number(column) and stand a chance of using that index in order to sort the data. See
</code> http://asktom.oracle.com/~tkyte/article1/index.html <code>
for more information on that feature.



Rating

  (30 ratings)

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

Comments

help for a specific sort required.

Bhawna Rajesh, May 14, 2002 - 1:59 am UTC

Hi Tom,

Our client has a column FUNDNUMBER, which is alphanumeric, contains values 0..99, 0A..0Z, 1A..1Z, ..so on, A..Z, A0..A9, B0..B9, ..so on, AA..AZ, BA..BZ,... ZA..ZZ
We are supposed to show the drop-down of FUNDNUMBER in this specific order. The normal orderby on fundnumber works fine except 0..99 range, where the string sorting does not give us desired result. After 1, 11 is shown.
Can we have the resultset retrived in the client-specific order by using some trick?

Please help us out, I know you are the only person who can.

Tom Kyte
May 14, 2002 - 6:02 am UTC

order by lpad(column,2) to make everything 2 characters long.  Consider:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x varchar2(2) );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t select rownum from all_users;

36 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select x from t order by x;

X
--
1
10
11
12
13
14
15
16
17
18
19
2
20
21
22
23
24
25
26
27
28
29
3
30
31
32
33
34
35
36
4
5
6
7
8
9

36 rows selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select x from t order by lpad( x, 2 );

X
--
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

36 rows selected.
 

sagi, May 16, 2002 - 7:27 am UTC

You are simply superb with answers are to the point.

Admirer of your knowledge.
Regards

We did the following

Murali, February 20, 2003 - 12:44 pm UTC

create table ting(tno varchar2(4));

insert into ting values('1');
insert into ting values('2');
insert into ting values('3');
insert into ting values('10');
insert into ting values('11');
insert into ting values('1a');
insert into ting values('20');
insert into ting values('2a');
insert into ting values('3a');
insert into ting values('10a');

We expect to see the order of the values as 1,1a,2,2a,3,3a.... and so on with your suggested solution. But what we have seen is the following order after we executed:

select tno from ting order by lpad(tno,4);

tno
----
1
2
3
10
11
1a
20
2a
3a
10a

What can we do to get the desired result?. Please suggest.

Tom Kyte
February 20, 2003 - 6:53 pm UTC

why?  That is not the way character strings sort. think "ascii collating sequence"

ops$tkyte@ORA920> select lpad(tno,4), tno from ting order by lpad(tno,4);

LPAD TNO
---- ----
   1 1
   2 2
   3 3
  10 10
  11 11
  1a 1a
  20 20
  2a 2a
  3a 3a
 10a 10a

10 rows selected.


That is just the way that string sorts...

So, it LOOKS to me like you want to sort this field AS IF it were 2 fields yes?  A number followed by a string?  Sort numerically and within the number by string?

1
1   a
2   
2   a
3   
3   a
10
10  a
11
20

right?  if so, we'll just break it in two:

ops$tkyte@ORA920> select substr(tno,1,length(tno)-nvl(length(replace(translate(tno,'0123456789','0000000000'),'0','')),0)),
  2         substr(tno,1+length(tno)-nvl(length(replace(translate(tno,'0123456789','0000000000'),'0','')),0)) ,
  3         tno
  4    from ting
  5   order by
  6  to_number(substr(tno,1,length(tno)-nvl(length(replace(translate(tno,'0123456789','0000000000'),'0','')),0))),
  7         substr(tno,1+length(tno)-nvl(length(replace(translate(tno,'0123456789','0000000000'),'0','')),0)) NULLS FIRST
  8  /

SUBS SUBS TNO
---- ---- ----
1         1
1    a    1a
2         2
2    a    2a
3         3
3    a    3a
10        10
10   a    10a
11        11
20        20

10 rows selected.


And maybe next time think "2 fields = 2 columns.  Numbers should be stored in Number types and Strings in varchar2's and dates in dates"  and don't store data in such an awkward style ;)



 

appropriate query for Bhavna's requirement

Chandra, February 21, 2003 - 2:42 am UTC

I think a more appropriate query for the requirement of Bhavna is:

select x from t
order by decode(sign(ascii(substr(lpad(x, 2), 2))-64), 1,
lpad(x, 3, chr(58)), lpad(x, 2));

For eg:-
chandra@ora817> select x from t order by lpad(x, 2);

X
--
1
2
3
4
5
6
7
8
9
A
B
C
0A
0B
0C
10
11
12
13
14
A1
AA
B1
BB
C1
CC
DD


where as

chandra@ora817> select x from t
2 order by decode(sign(ascii(substr(lpad(x, 2), 2))-64), 1,
3 lpad(x, 3, chr(58)), lpad(x, 2));

X
--
1
2
3
4
5
6
7
8
9
10
11
12
13
14
0A
0B
0C
A
B
C
AA
BB
CC
DD
A1
B1
C1


Sorting -- sort a string like a number

A reader, December 09, 2003 - 10:32 am UTC

The last example was perfect for what I needed. Thank you !!

Sort String

Dennis, June 14, 2005 - 7:06 am UTC

These are the sample data for the ‘job tm description’ column. In that after Licensing Team 1, Licensing Team 10 is displaying…..

It has to come after Licensing Team 3. And Special Firms also.



Job_tm_dscrptn: (Normal query will display given below)



Address Changes

Agent Change

GP/CGA

Licensing Team 1

Licensing Team 10

Licensing Team 2

Licensing Team 3

SRH

Special Firms Team 1

Special Firms Team 10

Special Firms Team 2

Special Firms Team 3



Expected Result



Address Changes

Agent Change

GP/CGA

Licensing Team 1

Licensing Team 2

Licensing Team 3

Licensing Team 10

SRH

Special Firms Team 1

Special Firms Team 2

Special Firms Team 3

Special Firms Team 10



How we can accomplish this???????????????????




Tom Kyte
June 14, 2005 - 10:02 am UTC

you tell me, describe to me a generic algorithm that can be used on any of your strings in order to break it into N columns that can be sorted (eg: state in text the requirements and the rules)

I mean, anytime the string has a number it should be sorted as a number?
can any string have more string data after the number?
can a string have more than one number in it?

(and most importantly, why wasn't this part of the design.... :)

Alphanumeric Sort

Dennis, June 15, 2005 - 1:24 am UTC

Your Question:

I mean, anytime the string has a number it should be sorted as a number?
can any string have more string data after the number?
can a string have more than one number in it?

My Answer:
I am sorry. String suffixed by number and string with infixed number to be sorted out.


Tom Kyte
June 15, 2005 - 3:21 am UTC

eh?

more details.... be more "clear", more "precise"

Dennis, June 15, 2005 - 6:58 am UTC

Expected Result
Address Changes
Agent Change
GP/CGA
Licensing Team 1
Licensing Team 2
Licensing Team 3
Licensing Team 10
MN1C
MN2C
MN3C
SRH
Special Firms Team 1
Special Firms Team 2
Special Firms Team 3
Special Firms Team 10

String and Numbers can be infix (MN1C) or suffix (Special Firms Team 10) or just a simple string.


Tom Kyte
June 15, 2005 - 9:53 am UTC

so, what if you have MN10C as well.



mixed alphanumeric/numeric sort

Urs, June 15, 2005 - 9:18 am UTC

ok, I don't like that either, but it works:

SQL> SELECT   t
  2      FROM texte
  3  ORDER BY t;

T
--------------------------------------------------------------------------------
Agent Change
GP/CGA
Licensing Team 1
Licensing Team 1 ab
Licensing Team 1 b
Licensing Team 10
Licensing Team 1ab
Licensing Team 2
Licensing Team 3
MN1C
MN2C
MN3C
SRH
Special Firms Team 1
Special Firms Team 10
Special Firms Team 2
Special Firms Team 3

17 rows selected.

Elapsed: 00:00:00.32
SQL> SELECT   t
  2      FROM (SELECT t, startnum, nonumtext, numtext, moretext,
  3                   DECODE(moretext,
  4                          NULL, TO_NUMBER(numtext),
  5                          TO_NUMBER(SUBSTR(numtext, 1, INSTR(numtext, moretext) - 1))) AS numval
  6              FROM (SELECT t, startnum, nonumtext, numtext,
  7                           LTRIM(TRANSLATE(numtext, '0123456789', '0000000000'), '0') AS moretext
  8                      FROM (SELECT t, startnum, SUBSTR(t, 1, startnum - 1) AS nonumtext,
  9                                   SUBSTR(t, startnum) AS numtext
 10                              FROM (SELECT t,
 11                                           DECODE(INSTR(TRANSLATE(t, '0123456789', '0000000000'), '0'),
 12                                                  0, 9999,
 13                                                  INSTR(TRANSLATE(t, '0123456789', '0000000000'), '0'))
 14                                                                                           AS startnum
 15                                      FROM texte))))
 16  ORDER BY nonumtext, numval, moretext;

T
--------------------------------------------------------------------------------
Agent Change
GP/CGA
Licensing Team 1
Licensing Team 1 ab
Licensing Team 1 b
Licensing Team 1ab
Licensing Team 2
Licensing Team 3
Licensing Team 10
MN1C
MN2C
MN3C
SRH
Special Firms Team 1
Special Firms Team 2
Special Firms Team 3
Special Firms Team 10

17 rows selected.

Elapsed: 00:00:00.00

hth, Urs 

yet another approach

Alexander, June 15, 2005 - 11:59 am UTC

create table a(c varchar2(30))
Table created

insert into a values('asd12341afsdf1wert')
1 row inserted


insert into a values('a9 afsdf1wert')
1 row inserted


insert into a values('a9 af9w 3ert')
1 row inserted


select c, translate(c,'0asdfwert ','0') tr from a
order by to_number(translate(c,'0asdfwert ','0'))
C TR
------------------------------ -------------------------------------------------
a9 afsdf1wert 91
a9 af9w 3ert 993
asd12341afsdf1wert 123411
3 rows selected


drop table a
Table dropped



yet another approach

Alexander, June 15, 2005 - 2:04 pm UTC

create table digits_list(n number(1));
insert into digits_list values(0);
insert into digits_list values(1);
insert into digits_list values(2);
insert into digits_list values(3);
insert into digits_list values(4);
insert into digits_list values(5);
insert into digits_list values(6);
insert into digits_list values(7);
insert into digits_list values(8);
insert into digits_list values(9);

create table power_10(n number(2), p10 number);
insert into power_10 values(1,1);
insert into power_10 values(2,10);
insert into power_10 values(3,100);
insert into power_10 values(4,1000);
insert into power_10 values(5,10000);
insert into power_10 values(6,100000);
insert into power_10 values(7,1000000);
insert into power_10 values(8,10000000);
insert into power_10 values(9,100000000);
insert into power_10 values(10,1000000000);

create table some_values(ph varchar2(9));

insert into some_values values('123a4560a');

insert into some_values values('(123)456-');

insert into some_values values('122.455a5');

insert into some_values values('321-?590a');

select ph, sum(digit_value*p10) cleansed_ph from
(select ph, to_number(substr(ph, n, 1)) digit_value,
row_number() over(partition by ph order by n desc) digit_pos
from some_values join digits_list
on substr(ph, n, 1) between '0' and '9' order by ph, n) digits_val
join power_10 on digit_pos=n
group by ph;

PH CLEANSED_PH
--------- -----------
(123)456- 123456
122.455a5 11224555
123a4560a 11234560
321-?590a 3321590
4 rows selected


drop table digits_list;

drop table power_10;

drop table some_values;


is there any generic solution (that can run on Dbs other than oracle) for this problem?

Rohit Seth, May 18, 2006 - 11:29 am UTC

I also have same requirement, i.e. sorting alphanumeric data like

1
10
PM10
PM11
2
3

and the expected result is

1
2
3
PM10
PM11

Using lpad will surely resolve the issue but is there any other solution that can run on DBs other than oracle (I read that T-SQL doesn't support lpad()), as our application need to support variety of DBs.

thanks
rohit

Tom Kyte
May 19, 2006 - 9:55 am UTC

good luck with that!

database independence implies you have an application that runs pretty poorly on one database and really really bad on the rest.

And you just made a really good point. Where are you going to find someone that is expert in all N databases so they can come up with mystical, magical, entirely non-performant generic solutions that work exactly the same on all databases? And then hopefully they never get hit by a bus....

order by

A reader, July 19, 2006 - 9:36 am UTC

I have values in a column of number data type as
1,2,3,4,5,6,0,0,0,0

when i select like

select * from t order by x;

0
0
0
1
2
3
4
5
6

are begin displayed

how can i display them as
1
2
3
4
5
6
0
0
0

Tom Kyte
July 19, 2006 - 1:30 pm UTC

order by decode( col, 0, 1, 0 ), col




Tom Please Help me with this query

Albert, February 12, 2007 - 7:27 am UTC

Tom,

I need help from you to write a complex SQL query (At least for me) here is the explaination

i have 3 tables

change_log,Activity,cdf_line

now cdf_line.cdf_id = activity.cdfid,
activity.activity_id=change_log.table_key_value,
and cdf_line.cdf_id=change_log.table_key_value

what i want to see is when i query records from change_log
i need to group cdf_id of cdf_line table and activity_id of activity table using the link of activity.cdfid=cdf_line.cdf_id

when i query the records i see all of them but value of table_key_value is not grouped with activity_id and its related cdf_id (the link is activity.cdfid=cdf_line.cdf_id)

table_key_value
------------------------
19879S23444 ----------(cdf_line.cdf_id)
190877S3456 ----------(cdf_line.cdf_id)
987Y7651222 ----------(cdf_line.cdf_id)
3478 --(activity.activity_id where cdfid 19879S23444)
9876 --(activity.activity_id where cdfid 190877S3456)
8765 --(activity.activity_id where cdfid 987Y7651222)

what i would like to see it like is
table_key_value
------------------------
19879S23444 ----------(cdf_line.cdf_id)
3478 --(activity.activity_id where cdfid 19879S23444)
190877S3456 ----------(cdf_line.cdf_id)
9876 --(activity.activity_id where cdfid 190877S3456)
987Y7651222 ----------(cdf_line.cdf_id)
8765 --(activity.activity_id where cdfid 987Y7651222)

Is it possible in SQL or am i even making sense to you here?

Thanks in advance
Tom Kyte
February 12, 2007 - 10:42 am UTC

no creates
no inserts
no look


A reader, February 12, 2007 - 11:37 am UTC

Sorry about that here are the details,

CDF_LINE
Name Null? Type
----------------------------------------- -------- ----------------------------
CDF_ID NOT NULL CHAR(12)
CONTRACT_ID NOT NULL CHAR(8)
SCHEDTYPE NOT NULL CHAR(2)
LINENUMBER NOT NULL CHAR(2)
CATALOGNUMBER NOT NULL CHAR(7)
STATUS CHAR(3)
AUTOANS CHAR(1)
BILLSTART DATE
QUANTITY NUMBER(6)
DEVICECODE VARCHAR2(10)
SALETYPE CHAR(1)
BILLCMF CHAR(8)
LOCATIONCMF CHAR(8)
ONETIME NUMBER(9)
RECURRING NUMBER(9)
CONTACT_ID NOT NULL NUMBER(9)
CONTACT_METHOD_ID NUMBER(9)
LOGON VARCHAR2(20)
WORKSHEETS CHAR(1)
CHECKLIST CHAR(1)
PRODUCTVERSION VARCHAR2(15)
WHOUPDATE VARCHAR2(30)
DATEUPDATE DATE
LINE_SCHEDULE CHAR(4)
GROUP_ID NUMBER(9)
BILLED_FLAG CHAR(1)
MNTONLY CHAR(1)
SURVEY_SCORE NUMBER(5,2)
CREATED_BY VARCHAR2(30)
UPDATED_BY VARCHAR2(30)
CREATE_DATE DATE

ACTIVITY TABLE
Name Null? Type
----------------------------------------- -------- --------------------------
ACTIVITY_ID NOT NULL NUMBER(9)
PROJECT_ID NUMBER(9)
SUBPROJ_ID NUMBER(9)
CDFID CHAR(12)
CONTACT_ID NOT NULL NUMBER(9)
RO CHAR(1)
DESCRIPTION VARCHAR2(100)
ACTIVITY_TYPE_ID NUMBER(9)
NEEDEDBY DATE
PRIORITY CHAR(1)
SCHEDULED CHAR(5)
PHONE CHAR(15)
PASSCODE CHAR(10)
LOCATION VARCHAR2(20)
ESTDATE DATE
ACTDATE DATE
ESTTIME NUMBER(5)
ACTTIME NUMBER(8,2)
ESTDATECHANGED CHAR(1)
MILESTONE CHAR(1)
AFTERHOURS CHAR(1)
COMPLETER VARCHAR2(30)
WHOUPDATE VARCHAR2(30)
DATEUPDATE DATE
CONTACT_METHOD_ID NUMBER(9)
PHASE_ID NOT NULL NUMBER(9)
SEQUENCE NUMBER(3)
GROUP_ID NOT NULL NUMBER(9)
ROLE_ID NOT NULL NUMBER(9)
TRAINING_PARTICIPANTS NUMBER(3)
RELATIVE_DAYS NUMBER(3)
AE_START_DATE DATE
CREATED_BY VARCHAR2(30)
UPDATED_BY VARCHAR2(30)
CREATE_DATE DATE

CHANGE_LOG
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME VARCHAR2(30)
TABLE_KEY VARCHAR2(30)
CHANGES VARCHAR2(2000)
WHOUPDATE VARCHAR2(30)
DATEUPDATE DATE
TABLE_KEY_VALUE VARCHAR2(50)
CHANGE_LOG_ID NOT NULL NUMBER(9)

here in Change_log table i have few records like
TABLE_KEY_VALUE TABLE_KEY_VALUE
--------------------------------------------------
45020632SW08
300532
208363

45020632SW08
252476
1392318
21302
45020632SW09
300533


in CDF_LINE i have
CDF_ID
------------
38020387SW02
38020387SW03
38020387SW04
38020388SW01
38020389SW01
38020390EQ01
38020390SW01
38020390SW02
38020390SW03
38020391EQ01

and in Activity table i have
ACTIVITY_ID CDFID
----------- ------------
5350227 69033419SW50
5350228 69033419SW51
5350229 69033419SW52
5350230 69033419SW53
5350231 69033419SW54
5350232 69033419SW55
5350233 69033419SW56
5350234 69033419SW57
5350235 69033419SW58
5350236 69033419SW59

Well as it was explained earlier Table_key_value column of change_log table has cdf_id from cdf_line table and activity_id from activity table whose corresponding CDFID is in the same table activity (activity.cdfid=cdf_line.cdf_id)

so as you can see the data is fetched

TABLE_KEY_VALUE
--------------------------------------------------
45020632SW08
300532
208363

45020632SW08
252476
1392318
21302
45020632SW09
300533

what i want is to display table_key_values in such a way where ACTIVITY.ACTIVITY_ID comes first where ACTIVITY.CDFID is followed immediately for that activity_id

like this

table_key_value
-------------------------
252476 (activity.activity_id where activity.cdfid is "45020632SW08")
45020632SW08 (cdf_lines.cdf_id)
1392318(activity.activity_id where activity.cdfid is "45020632SW09")
21302
45020632SW09
300533
208364

Is it possible?

Sorting -- sort a string like a number.

Anand Kumar Barai, July 18, 2011 - 9:53 am UTC

Here is what I have in the database:
SQL> select x from table where x = '28 T1F FTMYFLMA01TTAMPFLXE ' order by x asc;

x
-----
1
10
11
12
13
14
15
16
17
18
19
1F
2
20
21
22
23
24
3
4
5
6
7
8
9

Now I will put the query that will sort the it with alpha numeric first and then the numbers and it did work for me(please see the query with the output:

SQL> select x from table where x = '28   T1F        FTMYFLMA01TTAMPFLXE   ' order by to_number(translate(x,translate(x,'a0123456789','a'),' ')), lpad(x,5);
x
-----
1F
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
The lpad(x,5) contains the column name x and the number of how many digits you want to filter. Ideally this should be the size of the column x but you can put a lower value also, say if your column is a VARCHAR2(5) then that can be put as 5, but it will yield the proper result even though you put the value as 4 or 3 or 2 as the highest size is 2 for all the values.

Tom Kyte
July 18, 2011 - 11:18 am UTC

SQL> select x from table where x = '28 T1F FTMYFLMA01TTAMPFLXE ' order by x 
asc;

x
-----
1
10
11


that is impossible.


x = '28 T1F ......'

x is not 1, 10, 11 and so on.



none of this makes sense, you need to

a) supply a create table
b) supply inserts into it
c) explain it a lot better.


Just a variant of "sorting by number"?

Duke Ganote, July 18, 2011 - 11:29 am UTC

Tom Kyte
July 18, 2011 - 11:32 am UTC

I don't know, it was so vague and contradictory - that I skipped it :)

Need help too

Jane, August 15, 2011 - 5:49 pm UTC

I need to sort values to.

3
4
5
6
50A
50
78
124

How to achieve this?
Tom Kyte
August 16, 2011 - 1:59 am UTC

well, you start by telling us what it means to sort them - what are your rules?

Regexp*2

Brendan, August 23, 2011 - 2:17 am UTC

This small variation on one of the methods in the other thread mentioned might be what you want:
WITH test AS (
SELECT    '3'    ver FROM DUAL UNION
SELECT    '4'        FROM DUAL UNION
SELECT    '5'        FROM DUAL UNION
SELECT    '6'        FROM DUAL UNION
SELECT    '50A'      FROM DUAL UNION
SELECT    '50'       FROM DUAL UNION
SELECT    '78'       FROM DUAL UNION
SELECT    '124'      FROM DUAL UNION
SELECT    '50A9ZZZZ' FROM DUAL UNION
SELECT    '50A10AAA' FROM DUAL
)
SELECT *
  FROM test
 ORDER BY RegExp_Replace (RegExp_Replace (ver, '(\d+)', '0000000\1.'), '0+(........)\.', '\1');

VER
--------
3
4
5
6
50
50A
50A9ZZZZ
50A10AAA
78
124

10 rows selected.

Tom Kyte
August 30, 2011 - 3:10 pm UTC

I frankly don't know what his "rules" for sorting are here..

Good/Another Option

Peter, March 29, 2013 - 5:26 pm UTC

I used the order by below to get the result in the num then alpha sort.

Order By Lpad(Regexp_Replace(col, '[[:alpha:]]',''), 10, '0'),
Regexp_Replace(col, '[[:digit:]]','') asc nulls first;

Natural Language Sort Type

Andrew Markiewicz, October 07, 2014 - 6:57 pm UTC

This is later than the original post but I have not seen any recent post about "natural" sorting (ie. numbers and text should sort as users would "expect"). We had a need for this so I created a UDT to effectively split strings on Number|Not Number to generate an array that can sort on each index individually. Comments in the UDT should explain hopefully in full.

Andrew
sql->create or replace type vc_coll as table of varchar2(32767)
  2  /

Type created.

Elapsed: 00:00:00.21
sql->
sql->create or replace type nlsort_obj as object
  2  (
  3    -------------------------------------------------------------------------------
  4    -- Program: nlsort_obj
  5    -- Description:
  6    --    Object type for Natural Language Sort.
  7    --    By default, Oracle will sort values in ASCII value order while many times
  8    --    users would expect a more natural ordering.
  9    --    Ex:
 10    --    ASCII  NLSort      NLSort (case insensitive)
 11    --      A1    A1          A1
 12    --      A10  A2          a1
 13    --      A101  A10        A2
 14    --      A2    A101        A10
 15    --      a1    a1          a11
 16    --      a11  a11        A101
 17    --
 18    -- nlsort_obj facilitates natural language sorting in SQL by passing the column name to sort.
 19    -- Optional parameters can modify the way it will sort.
 20    -- The constructor will split the text into array elements looking for two alternating groups.
 21    --    "Number" [0-9]
 22    --    "Not Number" [^0-9] (referenced as "Alpha" here out)
 23    --
 24    -- It will stop parsing when both the Number and Alpha search return null.
 25    -- The resultant parsing will produce an array of values to use for sorting with most significant
 26    -- in the lowest array index.  The parser will assume the text begins with Number.
 27    --
 28    --    Number | Alpha  | Number | Alpha | .... | null | null
 29    --
 30    --
 31    -- Constructor parameters:
 32    --  self     - object instance is implicit in call.  Do not provide in constructor call
 33    --       self is defined here to use the NOCOPY option to pass by reference.
 34    --  p_txt    - Text to sort.  Typically a column name but any text source can be provided.
 35    --  p_caseSensitive - [Y|N] Default Y. Controls whether the comparison will be case sensitive or not
 36    --  p_nullsFirst    - [Y|N] Default N. Matches Oracle's default.  Set to Y to sort null values last.
 37    --        This does not affect numeric values delimited with p_numberDelimiter. Numbers
 38    --        with p_numberDelimiter text delimiters will sort nulls first always to obtain
 39    --        a proper numeric sort.
 40    --  p_numberDelimiter - Text delimiter used to denote cascading scale in a number.  This will correctly sort
 41    --       floating point values (e.g. 10, 10.2, 10.21, etc) or version release numbers
 42    --       (12, 12.1.4.1, 12.3.41.0, etc).  The delimiter value defaults to '.' but any text delimiter
 43    --       up to 50 characters can be used. When the delimiter is recognized in the sort, it will
 44    --       automatically sort nulls first regardless of the the p_nullsFirst attribute.
 45    --       To remove any numeric delimiter set p_numberDelimiter to null.
 46    -------------------------------------------------------------------------------
 47    text     varchar2(32000)
 48    , valArr     vc_coll
 49    , caseSensitive    varchar2(1)
 50    , nullsFirst    varchar2(1)
 51    , numberDelimiter   varchar2(50)
 52    , order member function sort(p_a in nlsort_obj) return integer
 53    , constructor function nlsort_obj
 54    (
 55      self       in out nocopy nlsort_obj
 56    , p_txt      in varchar2
 57    , p_caseSensitive   in varchar2 := 'Y'
 58    , p_nullsFirst      in varchar2 := 'N'
 59    , p_numberDelimiter in varchar2 := '.'
 60    --, p_checkForDates   in varchar2 := 'N'
 61    )
 62    return self as result
 63  );
 64  /

Type created.

Elapsed: 00:00:00.01
sql->show err
No errors.
sql->
sql->create or replace type body nlsort_obj
  2  as
  3    ---------------------------------------------------------------------------
  4    order member function sort(p_a in nlsort_obj)
  5    return integer
  6    is
  7      idx        integer := 0;
  8      ret        integer := 0;
  9      numChk      Boolean := true; --false;
 10      numDelimChk Boolean := false;
 11    begin
 12      --dbms_output.put_line('compare self:p = ' || self.text || ':' || p_a.text);
 13  
 14      --
 15      -- Iterate over the elements of alpha and number values in the array
 16      -- until an order can be determined for the two comparators
 17      --
 18      idx := self.valArr.first;
 19      while (idx is not null)
 20      loop
 21        --dbms_output.put_line('idx=' || idx);
 22        --dbms_output.put_line('self,p_a=' ||self.valArr(idx) || ',' || p_a.valArr(idx));
 23        --dbms_output.put_line('numDelimChk=' || case when numDelimChk then 'True' else 'false' end);
 24  
 25        if not numChk and (self.valArr(idx) = self.numberDelimiter or p_a.valArr(idx) = p_a.numberDelimiter) then
 26          numDelimChk := true;
 27        end if;
 28  
 29        if self.valArr(idx) is null and p_a.valArr(idx) is null then
 30    null;  -- indeterminate
 31        elsif self.valArr(idx) is null then
 32          ret := case when numDelimChk or self.nullsFirst = 'Y' then -1 else 1 end;
 33          numDelimChk := false;
 34          exit;
 35        elsif p_a.valArr(idx) is null then
 36          ret := case when numDelimChk or p_a.nullsFirst = 'Y' then 1 else -1 end;
 37          numDelimChk := false;
 38          exit;
 39        elsif numChk then
 40          if to_number(self.valArr(idx)) < to_number(p_a.valArr(idx)) then
 41     ret := -1;
 42     exit;
 43          elsif to_number(self.valArr(idx)) > to_number(p_a.valArr(idx)) then
 44     ret := 1;
 45     exit;
 46          else
 47     null;
 48          end if;
 49  
 50        else
 51          if self.valArr(idx) < p_a.valArr(idx) then
 52     ret := -1;
 53     exit;
 54          elsif self.valArr(idx) > p_a.valArr(idx) then
 55     ret := 1;
 56     exit;
 57          else
 58     null;
 59          end if;
 60  
 61        end if;
 62  
 63        numChk := case when numChk then false else true end;
 64        idx := self.valArr.next(idx);
 65      end loop;
 66      return ret;
 67    end;
 68  
 69    ---------------------------------------------------------------------------
 70    constructor function nlsort_obj
 71    (
 72      self       in out nocopy nlsort_obj
 73    , p_txt      in varchar2
 74    , p_caseSensitive   in varchar2 := 'Y'
 75    , p_nullsFirst      in varchar2 := 'N'
 76    , p_numberDelimiter in varchar2 := '.'
 77    --, p_checkForDates   in varchar2 := 'N'
 78    )
 79    return self as result
 80    is
 81  
 82      --regText  varchar2(21)  := '([^0-9]+)[0-9]*';
 83      --regNum  varchar2(21)  := '([0-9]+)[^0-9]*';
 84      iter        integer      := 1;
 85      pat1        varchar2(21)  := '([0-9]+)[^0-9]*';
 86      pat2        varchar2(21)  := '([^0-9]+)[0-9]*';
 87      pat1Offset  integer      := 0;
 88    begin
 89      text     := p_txt;
 90      valArr    := vc_coll();
 91      caseSensitive   := case when p_caseSensitive in ('Y', 'N') then p_caseSensitive else 'Y' end;
 92      nullsFirst    := case when p_nullsFirst in ('Y', 'N') then p_nullsFirst else 'N' end;
 93      numberDelimiter := substr(p_numberDelimiter, 1,50);
 94  
 95      if caseSensitive = 'N' then
 96        text := upper(text);
 97      end if;
 98  
 99  
100      --
101      -- Always expect p_txt to start with number.  If it is Alpha the first element will just be null.
102      -- Iterate over text string until neither pat1 nor pat2 finds a match.
103      -- This will leave two null values at the end of the array which are left intentionally
104      -- to facilitate comparisons of strings with different counts of [Num|Text] pairs.
105      -- Without the nulls, the sort may attempt to use the greater count as the SELF parameter
106      -- and use an index that will be out of bounds for the shorter.  The null values at the end
107      -- of the array allow that comparison to happen without error.
108      --
109      loop
110        self.valArr.extend;
111        self.valArr(self.valArr.last) := regexp_substr(
112     self.text,
113     case when iter=1 then '^' else null end  ||pat1, 1,iter - pat1Offset,'i',1
114        );
115  
116        self.valArr.extend;
117        self.valArr(self.valArr.last) := regexp_substr(self.text, pat2,  1,iter,'i',1);
118  
119        exit when valArr(valArr.last) is null and valArr(valArr.last -1) is null;
120  
121        if iter =1 and self.valArr(1) is null then
122          --
123          -- This offset is needed when the starting element is a Alpha instead of Number.
124          -- The first element will be null for that value and so the
125          -- first occurrence was not yet found.  Without the offset to reduce the match occurrence,
126          -- the last element of this type will not be found and the element will be null while
127          -- pat2 could be found on the same iteration causing issues in correctly sorting.
128          --
129          pat1Offset := 1;
130        end if;
131        iter := iter + 1;
132  
133      end loop;
134  
135      return;
136    end;
137  end;
138  /

Type body created.

Elapsed: 00:00:00.03
sql->show err
No errors.
sql->
sql->col fld format a30
sql->
sql->----------------------------------------------------------------
sql->-- Test1 - Multiple delimited numeric, e.g. release number
sql->----------------------------------------------------------------
sql->with src as (
  2    select '12.3.4.1' fld, 1 srt from dual union all
  3    select '12.10.0.1' fld, 2 srt from dual union all
  4    select '12.1.4.1' fld, 3 srt from dual union all
  5    select '12' fld, 4 srt from dual union all
  6    select '12.3.41.0' fld, 5 srt from dual
  7  )
  8  select * from src
  9  order by fld
 10  ;

FLD                           |       SRT
------------------------------|----------
12                            |         4
12.1.4.1                      |         3
12.10.0.1                     |         2
12.3.4.1                      |         1
12.3.41.0                     |         5

Elapsed: 00:00:00.00
sql->
sql->
sql->with src as (
  2    select '12.3.4.1' fld, 1 srt from dual union all
  3    select '12.10.0.1' fld, 2 srt from dual union all
  4    select '12.1.4.1' fld, 3 srt from dual union all
  5    select '12' fld, 4 srt from dual union all
  6    select '12.3.41.0' fld, 5 srt from dual
  7  )
  8  select * from src
  9  order by nlssort(fld, 'nls_sort=BINARY')
 10  ;

FLD                           |       SRT
------------------------------|----------
12                            |         4
12.1.4.1                      |         3
12.10.0.1                     |         2
12.3.4.1                      |         1
12.3.41.0                     |         5

Elapsed: 00:00:00.00
sql->
sql->
sql->with src as (
  2    select '12.3.4.1' fld, 1 srt from dual union all
  3    select '12.10.0.1' fld, 2 srt from dual union all
  4    select '12.1.4.1' fld, 3 srt from dual union all
  5    select '12' fld, 4 srt from dual union all
  6    select '12.3.41.0' fld, 5 srt from dual
  7  )
  8  select * from src
  9  order by nlsort_obj(fld)
 10  ;

FLD                           |       SRT
------------------------------|----------
12                            |         4
12.1.4.1                      |         3
12.3.4.1                      |         1
12.3.41.0                     |         5
12.10.0.1                     |         2

Elapsed: 00:00:00.00
sql->
sql->----------------------------------------------------------------
sql->-- Test2 - Mixed alphanumeric and numeric delimiter
sql->----------------------------------------------------------------
sql->with src as (
  2    select 'A10.3.4.1' fld, 1 srt from dual union all
  3    select '12.10.0.1' fld, 2 srt from dual union all
  4    select '12.1.4.1' fld, 3 srt from dual union all
  5    select 'A10' fld, 4 srt from dual union all
  6    select 'B10' fld, 5 srt from dual union all
  7    select 'B1' fld, 6 srt from dual union all
  8    select 'B3' fld, 7 srt from dual union all
  9    select '12.3.41.0' fld, 8 srt from dual
 10  )
 11  select s.*
 12    from src s
 13  order by fld
 14  ;

FLD                           |       SRT
------------------------------|----------
12.1.4.1                      |         3
12.10.0.1                     |         2
12.3.41.0                     |         8
A10                           |         4
A10.3.4.1                     |         1
B1                            |         6
B10                           |         5
B3                            |         7

8 rows selected.

Elapsed: 00:00:00.00
sql->
sql->with src as (
  2    select 'A10.3.4.1' fld, 1 srt from dual union all
  3    select '12.10.0.1' fld, 2 srt from dual union all
  4    select '12.1.4.1' fld, 3 srt from dual union all
  5    select 'A10' fld, 4 srt from dual union all
  6    select 'B10' fld, 5 srt from dual union all
  7    select 'B1' fld, 6 srt from dual union all
  8    select 'B3' fld, 7 srt from dual union all
  9    select '12.3.41.0' fld, 8 srt from dual
 10  )
 11  select s.*
 12    from src s
 13  order by nlsort_obj(fld)
 14  ;

FLD                           |       SRT
------------------------------|----------
12.1.4.1                      |         3
12.3.41.0                     |         8
12.10.0.1                     |         2
A10                           |         4
A10.3.4.1                     |         1
B1                            |         6
B3                            |         7
B10                           |         5

8 rows selected.

Elapsed: 00:00:00.00
sql->
sql->with src as (
  2    select 'A10.3.4.1' fld, 1 srt from dual union all
  3    select '12.10.0.1' fld, 2 srt from dual union all
  4    select '12.1.4.1' fld, 3 srt from dual union all
  5    select 'A10' fld, 4 srt from dual union all
  6    select 'B10' fld, 5 srt from dual union all
  7    select 'B1' fld, 6 srt from dual union all
  8    select 'B3' fld, 7 srt from dual union all
  9    select '12.3.41.0' fld, 8 srt from dual
 10  )
 11  select s.*
 12    from src s
 13  order by nlsort_obj(fld, p_nullsFirst => 'Y')
 14  ;

FLD                           |       SRT
------------------------------|----------
A10                           |         4
A10.3.4.1                     |         1
B1                            |         6
B3                            |         7
B10                           |         5
12.1.4.1                      |         3
12.3.41.0                     |         8
12.10.0.1                     |         2

8 rows selected.

Elapsed: 00:00:00.00
sql->
sql->----------------------------------------------------------------
sql->-- Test3 - change the numeric delimiter
sql->----------------------------------------------------------------
sql->with src as (
  2    select '12a3a4a1' fld, 1 srt from dual union all
  3    select '12a10a0a1' fld, 2 srt from dual union all
  4    select '12a1a4a1' fld, 3 srt from dual union all
  5    select '12' fld, 4 srt from dual union all
  6    select '12a3a41a0' fld, 5 srt from dual
  7  )
  8  select * from src
  9  order by nlsort_obj(fld)
 10  ;

FLD                           |       SRT
------------------------------|----------
12a1a4a1                      |         3
12a3a4a1                      |         1
12a3a41a0                     |         5
12a10a0a1                     |         2
12                            |         4

Elapsed: 00:00:00.00
sql->
sql->with src as (
  2    select '12a3a4a1' fld, 1 srt from dual union all
  3    select '12a10a0a1' fld, 2 srt from dual union all
  4    select '12a1a4a1' fld, 3 srt from dual union all
  5    select '12' fld, 4 srt from dual union all
  6    select '12a3a41a0' fld, 5 srt from dual
  7  )
  8  select * from src
  9  order by nlsort_obj(fld, p_numberDelimiter=>'a')
 10  ;

FLD                           |       SRT
------------------------------|----------
12                            |         4
12a1a4a1                      |         3
12a3a4a1                      |         1
12a3a41a0                     |         5
12a10a0a1                     |         2

Elapsed: 00:00:00.00
sql->
sql->----------------------------------------------------------------
sql->-- Test4 - Alph numeric with upper and lower case
sql->----------------------------------------------------------------
sql->with src as (
  2    select 'A1' fld, 1 srt from dual union all
  3    select 'A10' fld, 2 srt from dual union all
  4    select 'A100' fld, 3 srt from dual union all
  5    select 'A2' fld, 4 srt from dual union all
  6    select 'a1' fld, 5 srt from dual union all
  7    select 'a11' fld, 6 srt from dual
  8  )
  9  select * from src
 10  order by fld
 11  ;

FLD                           |       SRT
------------------------------|----------
A1                            |         1
A10                           |         2
A100                          |         3
A2                            |         4
a1                            |         5
a11                           |         6

6 rows selected.

Elapsed: 00:00:00.00
sql->
sql->with src as (
  2    select 'A1' fld, 1 srt from dual union all
  3    select 'A10' fld, 2 srt from dual union all
  4    select 'A100' fld, 3 srt from dual union all
  5    select 'A2' fld, 4 srt from dual union all
  6    select 'a1' fld, 5 srt from dual union all
  7    select 'a11' fld, 6 srt from dual
  8  )
  9  select * from src
 10  order by nlsort_obj(fld)
 11  ;

FLD                           |       SRT
------------------------------|----------
A1                            |         1
A2                            |         4
A10                           |         2
A100                          |         3
a1                            |         5
a11                           |         6

6 rows selected.

Elapsed: 00:00:00.00
sql->
sql->with src as (
  2    select 'A1' fld, 1 srt from dual union all
  3    select 'A10' fld, 2 srt from dual union all
  4    select 'A100' fld, 3 srt from dual union all
  5    select 'A2' fld, 4 srt from dual union all
  6    select 'a1' fld, 5 srt from dual union all
  7    select 'a11' fld, 6 srt from dual
  8  )
  9  select * from src
 10  order by nlsort_obj(fld, p_caseSensitive=>'N')
 11  ;

FLD                           |       SRT
------------------------------|----------
A1                            |         1
a1                            |         5
A2                            |         4
A10                           |         2
a11                           |         6
A100                          |         3

6 rows selected.

Elapsed: 00:00:00.00
sql->
sql->spool off

solution can be tried with regexp_replace function

Vamsi Krishna Vallakavi, April 15, 2015 - 10:32 am UTC

The solution can be written as below

select column,.., from table
order by to_number(nvl(trim(regexp_replace(column,'[A-Za-z]')),0)) asc

Works very well, but not for some instances

Ben Waswa, April 17, 2015 - 9:12 pm UTC

Vamsi, your solution is very good. I do, however, find that it does not work very well for some situations. Think of a list of 8 items e.g. BR00.1, BR00.2, BRBF.1, BR00.5, BRIP.1, BR01.1, BRRF.1, BR01.2 for instance.
Your solution results in this order:
BRRF.1,BR00.1,BRBF.1,BRIP.1,BR00.2,BR00.5,BR01.1,BR02.1
instead of :
BR00.1,BR00.2,BR00.5,BR01.1,BR02.1,BRBF.1,BRIP.1,BRRF.1.

How can you tweak to get the 'natural order'?

ASC/Desc in order by clause

Rajeshwaran, Jeyabal, March 07, 2016 - 12:07 pm UTC

Team,

Currently i am using IF/ELSIF construct to do sorting in ASC and DESC order, is it possible to do that in a single block rather than having IF/ELSIF block to achieve it ?

rajesh@ORA11G> variable x refcursor
rajesh@ORA11G> variable p_in number
rajesh@ORA11G> variable p_sort number
rajesh@ORA11G> exec :p_in :=1; :p_sort := 2;

PL/SQL procedure successfully completed.

rajesh@ORA11G> begin
  2    if :p_sort = 1 then
  3      open :x for
  4        select empno,ename,hiredate,sal from emp
  5          order by decode(:p_in,2,ename,
  6              1,to_char(empno),
  7              3,to_char(hiredate,'yyyymmddhh24miss')) asc ;
  8    elsif :p_sort = 2 then
  9      open :x for
 10        select empno,ename,hiredate,sal from emp
 11          order by decode(:p_in,2,ename,
 12              1,to_char(empno),
 13              3,to_char(hiredate,'yyyymmddhh24miss')) desc ;
 14    end if;
 15  end;
 16  /

PL/SQL procedure successfully completed.

rajesh@ORA11G> print x

     EMPNO ENAME      HIREDATE                      SAL
---------- ---------- ---------------------- ----------
      7934 MILLER     01/23/1982 12:00:00 AM       1300
      7902 FORD       12/03/1981 12:00:00 AM       3000
      7900 JAMES      12/03/1981 12:00:00 AM        950
      7876 ADAMS      05/23/1987 12:00:00 AM       1100
      7844 TURNER     09/08/1981 12:00:00 AM       1500
      7839 KING       11/17/1981 12:00:00 AM       5000
      7788 SCOTT      04/19/1987 12:00:00 AM       3000
      7782 CLARK      06/09/1981 12:00:00 AM       2450
      7698 BLAKE      05/01/1981 12:00:00 AM       2850
      7654 MARTIN     09/28/1981 12:00:00 AM       1250
      7566 JONES      04/02/1981 12:00:00 AM       2975
      7521 WARD       02/22/1981 12:00:00 AM       1250
      7499 ALLEN      02/20/1981 12:00:00 AM       1600
      7369 SMITH      12/17/1980 12:00:00 AM        800

14 rows selected.

rajesh@ORA11G>

Connor McDonald
March 07, 2016 - 5:42 pm UTC

One way is to have a separate order by clause for each direction. These check the value of :p_sort, e.g.:

SQL> variable p_in number
SQL> variable p_sort number
SQL> exec :p_sort := 2;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.22
SQL> exec :p_in := 3;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.20
SQL>
SQL> select empno, ename, hiredate, sal
  2  from scott.emp
  3  order by
  4    case
  5      when :p_sort = 1 then
  6      decode(:p_in,2,ename,
  7             1,to_char(empno),
  8             3,to_char(hiredate,'yyyymmddhh24miss'))
  9    end,
 10    case
 11      when :p_sort = 2 then
 12      decode(:p_in,2,ename,
 13             1,to_char(empno),
 14             3,to_char(hiredate,'yyyymmddhh24miss'))
 15    end desc;

     EMPNO ENAME      HIREDATE         SAL
---------- ---------- --------- ----------
      7876 ADAMS      23-MAY-87       1100
      7788 SCOTT      19-APR-87       3000
      7934 MILLER     23-JAN-82       1300
      7902 FORD       03-DEC-81       3000
      7900 JAMES      03-DEC-81        950
      7839 KING       17-NOV-81       5000
      7654 MARTIN     28-SEP-81       1250
      7844 TURNER     08-SEP-81       1500
      7782 CLARK      09-JUN-81       2450
      7698 BLAKE      01-MAY-81       2850
      7566 JONES      02-APR-81       2975
      7521 WARD       22-FEB-81       1250
      7499 ALLEN      20-FEB-81       1600
      7369 SMITH      17-DEC-80        800

14 rows selected.

Need help with varchar2 sort

VD, May 06, 2016 - 1:23 am UTC

Hi Tom,
I have created a test table with a varchar2 field and inserted data into it to replicate a real world set of data I am working with.

create table test_table (str_field VARCHAR2(20) NOT NULL);


insert into test_table (str_field) values ('1.1.7');
insert into test_table (str_field) values ('1.1.8');
insert into test_table (str_field) values ('1.1.9');
insert into test_table (str_field) values ('1.1.10');
insert into test_table (str_field) values ('1.2.1');
insert into test_table (str_field) values ('1.2.2');
insert into test_table (str_field) values ('1.6.1');
insert into test_table (str_field) values ('1.10.1');
insert into test_table (str_field) values ('1.10.2');
insert into test_table (str_field) values ('1.20.1');


When I do the following select I get the sort noted below.

SQL> select str_field from test_table order by LPAD(str_field, 10);

STR_FIELD
--------------------
1.1.7
1.1.8
1.1.9
1.2.1
1.2.2
1.6.1
1.1.10
1.10.1
1.10.2
1.20.1

10 rows selected.

But the sort I need to produce for my work is as follows. i.e. 1.1.10 should be after 1.1.9 and not after 1.6.1.

1.1.7
1.1.8
1.1.9
1.1.10
1.2.1
1.2.2
1.6.1
1.10.1
1.10.2
1.20.1

A little bit more explanation:
If we have a 1.1.1 and a 1.1.1.1, they would need to be listed one after the other before 1.1.2 and so on. The most in the string would be ie. 1.2.3.1 (4 places) never would we have 5 (1.2.3.1.2).

Is there a way to achieve this? If yes, could you give please me suggestions for the same? I would really appreciate your input.

Thank you in advance!
Connor McDonald
May 06, 2016 - 2:08 am UTC


SQL> drop table test_table purge;

Table dropped.

SQL>
SQL> create table test_table (str_field VARCHAR2(20) NOT NULL);

Table created.

SQL>
SQL> insert into test_table (str_field) values ('1');

1 row created.

SQL> insert into test_table (str_field) values ('1.1');

1 row created.

SQL> insert into test_table (str_field) values ('1.1.7');

1 row created.

SQL> insert into test_table (str_field) values ('1.1.8');

1 row created.

SQL> insert into test_table (str_field) values ('1.1.9');

1 row created.

SQL> insert into test_table (str_field) values ('1.1.10');

1 row created.

SQL> insert into test_table (str_field) values ('1.2.1');

1 row created.

SQL> insert into test_table (str_field) values ('1.2.2');

1 row created.

SQL> insert into test_table (str_field) values ('1.6.1');

1 row created.

SQL> insert into test_table (str_field) values ('1.10.1');

1 row created.

SQL> insert into test_table (str_field) values ('1.10.2');

1 row created.

SQL> insert into test_table (str_field) values ('1.20.1');

1 row created.

SQL> insert into test_table (str_field) values ('1.25.3.4');

1 row created.

SQL> insert into test_table (str_field) values ('1.25.4.1');

1 row created.

SQL>
SQL> select str_field
  2  from
  3  (
  4    select str_field,
  5      case when i1 > 0 then substr(str_field,1,i1-1) else str_field end c1,
  6      case when i2 > 0 then substr(str_field,i1+1,i2-i1-1)
  7           when i1 > 0 then substr(str_field,i1+1) end c2,
  8      case when i3 > 0 then substr(str_field,i2+1,i3-i2-1)
  9           when i2 > 0 then substr(str_field,i2+1) end c3,
 10      case when i3 > 0 then substr(str_field,i3+1) end c4
 11    from (
 12      select
 13        str_field,
 14        instr(str_field,'.',1,1) i1,
 15        instr(str_field,'.',1,2) i2,
 16        instr(str_field,'.',1,3) i3
 17      from test_table
 18    )
 19  )
 20  order by lpad(c1,4), lpad(c2,4) nulls first, lpad(c3,4) nulls first, lpad(c4,4) nulls first
 21  /

STR_FIELD
--------------------
1
1.1
1.1.7
1.1.8
1.1.9
1.1.10
1.2.1
1.2.2
1.6.1
1.10.1
1.10.2
1.20.1
1.25.3.4
1.25.4.1

14 rows selected.

SQL>


Need help with varchar2 sort (Continued)

VD, May 07, 2016 - 5:19 am UTC

Hi Tom,

Thank you so much for the previous suggestion provided. It works perfectly on it`s own. When I add it along with other order by clauses I have in place in my real world query, I am unable to get it to work. When I wrote to you yesterday, I did not mention about the other sorts I have to also work with. My intention yesterday was to try to incorporate any suggestion you would make, to what I already have in place without making explanation too complicated. But that did not work and now I am trying to explain the whole story.

Please find below my new table and data.

create table new_test_table
(
invoice_id NUMBER NOT NULL,
site VARCHAR2(5) NOT NULL,
str_field_ref VARCHAR2(20) NULL,
str_field_udfa VARCHAR2(20) NULL
);

insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124565', '01', '1.1.7', 'Case');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124565', '01', '1.1.8', 'Case');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124565', '01', '1.1.9', 'Case');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124565', '01', '1.1.10', 'Case');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124565', '01', '1.2.1', 'Case');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124565', '01', '1.2.2', 'Case');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124565', '01', '1.6.1', 'Case');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124565', '01', '1.10.1', 'Case');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124565', '01', '1.10.2', 'Case');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124565', '01', '1.20.1', 'Case');

insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124570', '02', '1', 'System');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124570', '02', '2', 'Evaporator');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124570', '02', '11', 'System');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124570', '02', '3', 'Evaporator');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124570', '02', '4', 'Evaporator');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124570', '02', '5', 'Evaporator');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124570', '02', '10', 'Condenser');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124570', '02', '13', 'Other');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124570', '02', '15', 'Other');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124570', '02', '15', 'Other');
insert into new_test_table (invoice_id, site, str_field_ref, str_field_udfa) values ('124570', '02', '12', 'Salable');

Consider this table as containing values for different invoice_ids. Each invoice has data pertaining to either site `01` or `02` and has a unique invoice_id.

The sorting requirement for my work (a report) is dependent upon, first and foremost, by the value of site.

For site `01`, the sorting order should depend upon str_field_ref which is exactly the scenario I explained yesterday in my question. i.e. str_field_ref field defined in today`s table is exactly as the str_field I defined yesterday. (i.e. If we have a 1.1.1 and a 1.1.1.1 in str_field_ref, they would need to be listed one after the other before 1.1.2 and so on. The most in the string would be ie. 1.2.3.1 (4 places) never would we have 5 (1.2.3.1.2). )

When a record is from site `01`, the value in field str_field_udfa is not important and not at all considered for any sorting purposes.

One other important point I forgot to mention yesterday was that if str_field_ref has a number value without any '.' characters, they should be listed below all of the ones with a '.'. For example, if we had a list like 1.1, 1.1.1, 1.6.1 and 1, then 1 should be displayed after all records 1.1, 1.1.1 & 1.6.1

for e.g. what I need is,
1.1
1.1.1
1.6.1
1

Also there can be nulls in str_field_ref.

For site `02`, the sorting order depends on the value of str_field_udfa. In the report, first records of type `System` should be displayed and then of type `Evaporator`, then `Condenser`, then `Other` and then any others.

To achieve all above requirements, I had set an order by clause as outlined below.

Order By
case
when site = '02' then
(Case When str_field_udfa ='System' Then '1'
When str_field_udfa = 'Evaporator' Then '2'
When str_field_udfa = 'Condenser' Then '3'
When str_field_udfa = 'Other' Then '4'
else '5' end)
end,
case
when site = '01' then
(case When instr(str_field_ref, '.') != 0 then LPAD(str_field_ref, 10) else null end) end,
case
when site='01' then (case When instr(str_field_ref, '.') = 0 then LPAD(str_field_ref, 10) else null end)
end;

If I run the following,

select * from new_test_table
where invoice_id = '124570'
Order By
case
when site = '02' then
(Case When str_field_udfa ='System' Then '1'
When str_field_udfa = 'Evaporator' Then '2'
When str_field_udfa = 'Condenser' Then '3'
When str_field_udfa = 'Other' Then '4'
else '5' end)
end,
case
when site = '01' then
(case When instr(str_field_ref, '.') != 0 then LPAD(str_field_ref, 10) else null end) end,
case
when site='01' then (case When instr(str_field_ref, '.') = 0 then LPAD(str_field_ref, 10) else null end)
end;

I get the following and that is exactly what I need for site `02`,

INVOICE_ID SITE STR_FIELD_REF STR_FIELD_UDFA
---------- ----- -------------------- --------------------
124570 02 1 System
124570 02 11 System
124570 02 2 Evaporator
124570 02 3 Evaporator
124570 02 4 Evaporator
124570 02 5 Evaporator
124570 02 10 Condenser
124570 02 13 Other
124570 02 15 Other
124570 02 15 Other
124570 02 12 Salable

11 rows selected.


If I run the following,

select * from new_test_table
where invoice_id = '124565'
Order By
case
when site = '02' then
(Case When str_field_udfa ='System' Then '1'
When str_field_udfa = 'Evaporator' Then '2'
When str_field_udfa = 'Condenser' Then '3'
When str_field_udfa = 'Other' Then '4'
else '5' end)
end,
case
when site = '01' then
(case When instr(str_field_ref, '.') != 0 then LPAD(str_field_ref, 10) else null end) end,
case
when site='01' then (case When instr(str_field_ref, '.') = 0 then LPAD(str_field_ref, 10) else null end)
end;

I get,
INVOICE_ID SITE STR_FIELD_REF STR_FIELD_UDFA
---------- ----- -------------------- --------------------
124565 01 1.1.7 Case
124565 01 1.1.8 Case
124565 01 1.1.9 Case
124565 01 1.2.1 Case
124565 01 1.2.2 Case
124565 01 1.6.1 Case
124565 01 1.1.10 Case
124565 01 1.10.1 Case
124565 01 1.10.2 Case
124565 01 1.20.1 Case

10 rows selected.

Since this is not what I want when I am working with site `01` as it has 1.1.10 after 1.6.1 instead of after 1.1.9, I tried to incorporate your suggestion from yesterday. In order to make that incorporation easier, I also created a TEST API with one function which can pull a substring of a nth position from string of values delimited by a given delimiter.

For e.g.

Test_Api.Get_Substring_At_Position('3.2.4', 1, '.') will return 3
Test_Api.Get_Substring_At_Position('1.10.3', 2, '.') will return 10
Test_Api.Get_Substring_At_Position('1.10.6', 3, '.') will return 6
Test_Api.Get_Substring_At_Position('5', 1, '.') will return 5

CREATE OR REPLACE PACKAGE Test_Api IS

FUNCTION Get_Substring_At_Position(string_ IN VARCHAR2,
position_ IN NUMBER,
delimeter_ IN VARCHAR2 DEFAULT '.') RETURN VARCHAR2;

END Test_Api;
/

CREATE OR REPLACE PACKAGE BODY Test_Api IS

FUNCTION Get_Substring_At_Position(string_ IN VARCHAR2,
position_ IN NUMBER,
delimeter_ IN VARCHAR2 DEFAULT '.') RETURN VARCHAR2
IS
sub_string_ VARCHAR(1000) := '';
start_pos_ NUMBER := 0;
end_pos_ NUMBER := 0;
length_ NUMBER := 0;
BEGIN
if (position_ <= 0) then
return '';
end if;

if (position_ = 1) then
start_pos_ := 0;
else
start_pos_ := instr(string_ || delimeter_, delimeter_ , 1, position_ - 1);
end if;

end_pos_ := instr(string_ || delimeter_, delimeter_ , 1, position_);
length_ := end_pos_ - start_pos_ - 1;

sub_string_ := substr(string_ || delimeter_, start_pos_ + 1, length_);

return sub_string_;
END Get_Substring_At_Position;

END Test_Api;
/

Following is how I tried to incorporate your suggestion, but I get an error of `ORA-00907: missing right parenthesis` at the comma after (lpad(Test_api.Get_Substring_At_Position(str_field_ref, 1, '.'),4).

select * from new_test_table
where invoice_id = '124565'
Order By
case
when site = '02' then
(Case When str_field_udfa ='System' Then '1'
When str_field_udfa = 'Evaporator' Then '2'
When str_field_udfa = 'Condenser' Then '3'
When str_field_udfa = 'Other' Then '4'
else '5' end)
end,
case
when site = '01' then
(case When instr(str_field_ref, '.') != 0 then
--LPAD(str_field_ref, 10) else null end) end,
(lpad(Test_api.Get_Substring_At_Position(str_field_ref, 1, '.'),4),
lpad(Test_api.Get_Substring_At_Position(str_field_ref, 2, '.'),4) nulls first, lpad(Test_api.Get_Substring_At_Position(str_field_ref, 3, '.'),4) nulls first, lpad(Test_api.Get_Substring_At_Position(str_field_ref, 4, '.'),4) nulls first) else null end) end,

case
when site='01' then (case When instr(str_field_ref, '.') = 0 then LPAD(str_field_ref, 10) else null end)
end;

It looks like when using a case statement for ordering, there cannot be multiple fields to order by with.

Is there anyway, I can incorporate your suggestion while still observing the other sorting requirements for site `02` and site `01`? Or else is there altogether a new way to do this?

Again I highly appreciate any help you can provide on this issue.

Connor McDonald
May 07, 2016 - 8:20 am UTC

Something like this ?

SQL> select site,str_field_ref, str_field_udfa,
  2     case
  3       when site = '02' then decode(str_field_udfa,'System','1','Evaporator','2','Condenser','3','Other','4','5')
  4     end||
  5     case when c2 is not null then lpad(c1,4,'0')||nvl(lpad(c2,4,'0'),'0000')||nvl(lpad(c3,4,'0'),'0000')||nvl(lpad(c4,4,'0'),'0000')
  6          else lpad(c1,4,'0')||'9999'
  7     end sortkey
  8  from
  9  (
 10    select str_field_ref,site,str_field_udfa,
 11      case when i1 > 0 then substr(str_field_ref,1,i1-1) else str_field_ref end c1,
 12      case when i2 > 0 then substr(str_field_ref,i1+1,i2-i1-1)
 13           when i1 > 0 then substr(str_field_ref,i1+1) end c2,
 14      case when i3 > 0 then substr(str_field_ref,i2+1,i3-i2-1)
 15           when i2 > 0 then substr(str_field_ref,i2+1) end c3,
 16      case when i3 > 0 then substr(str_field_ref,i3+1) end c4
 17    from (
 18      select
 19        str_field_ref,site,str_field_udfa,
 20        instr(str_field_ref,'.',1,1) i1,
 21        instr(str_field_ref,'.',1,2) i2,
 22        instr(str_field_ref,'.',1,3) i3
 23      from new_test_table
 24    )
 25  )
 26  order by site,sortkey
 27  /

SITE  STR_FIELD_REF        STR_FIELD_UDFA       SORTKEY
----- -------------------- -------------------- -----------------
01    1.1.7                Case                 0001000100070000
01    1.1.8                Case                 0001000100080000
01    1.1.9                Case                 0001000100090000
01    1.1.10               Case                 0001000100100000
01    1.2.1                Case                 0001000200010000
01    1.2.2                Case                 0001000200020000
01    1.6.1                Case                 0001000600010000
01    1.10.1               Case                 0001001000010000
01    1.10.2               Case                 0001001000020000
01    1.20.1               Case                 0001002000010000
02    1                    System               100019999
02    11                   System               100119999
02    2                    Evaporator           200029999
02    3                    Evaporator           200039999
02    4                    Evaporator           200049999
02    5                    Evaporator           200059999
02    10                   Condenser            300109999
02    13                   Other                400139999
02    15                   Other                400159999
02    15                   Other                400159999
02    12                   Salable              500129999

21 rows selected.



Cheers,
Connor

Need help with varchar2 sort (Resolved!!!)

VD, May 09, 2016 - 3:42 pm UTC

Connor,

Your suggestion resolved my sorting dilemma!!! I cannot say how grateful I am.

Thank you so much for your generosity,
VD
Chris Saxon
May 10, 2016 - 12:59 am UTC

Glad we could help

Need help with varchar2 sort | Alternatives

Rajeshwaran, Jeyabal, May 10, 2016 - 3:08 pm UTC

Sorry for being late in the game, Here are some other alternatives.

rajesh@ORA11G> select t.* ,
  2  regexp_replace( regexp_replace(str_field_ref||'.','(\d+\.)','0000\1'),
  3    '0+(....)\.','\1') as col3
  4  from new_test_table t
  5  order by
  6    case when site ='01' then 1
  7         when site ='02' and str_field_udfa ='System' then 2
  8         when site ='02' and str_field_udfa ='Evaporator' then 3
  9         when site ='02' and str_field_udfa ='Condenser' then 4
 10         when site ='02' and str_field_udfa ='Other' then 5
 11         else 6 end , col3
 12  /

INVOICE_ID SITE  STR_FIELD_REF        STR_FIELD_UDFA       COL3
---------- ----- -------------------- -------------------- ---------------
    124565 01    1.1.7                Case                 000100010007
    124565 01    1.1.8                Case                 000100010008
    124565 01    1.1.9                Case                 000100010009
    124565 01    1.1.10               Case                 000100010010
    124565 01    1.2.1                Case                 000100020001
    124565 01    1.2.2                Case                 000100020002
    124565 01    1.6.1                Case                 000100060001
    124565 01    1.10.1               Case                 000100100001
    124565 01    1.10.2               Case                 000100100002
    124565 01    1.20.1               Case                 000100200001
    124570 02    1                    System               0001
    124570 02    11                   System               0011
    124570 02    2                    Evaporator           0002
    124570 02    3                    Evaporator           0003
    124570 02    4                    Evaporator           0004
    124570 02    5                    Evaporator           0005
    124570 02    10                   Condenser            0010
    124570 02    13                   Other                0013
    124570 02    15                   Other                0015
    124570 02    15                   Other                0015
    124570 02    12                   Salable              0012

21 rows selected.

rajesh@ORA11G>

Need help with varchar2 sort | Alternatives

VD, May 10, 2016 - 4:05 pm UTC

Thank you for your input Rajesh. I appreciate you taking the time to suggest an alternative.

A reader, November 08, 2018 - 4:02 pm UTC

Create a program in Java that will enable CMD input of 1 String ("ASC” or "DESC”) and 5 numbers. As a result, the numbers will be ordered in ascending or descending order, depending on whether "ASC” or "DESC” has been entered. The case of the String is irrelevant
Connor McDonald
November 09, 2018 - 2:20 am UTC

This sounds a lot like..... homework.