Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Karthik.

Asked: December 22, 2002 - 10:17 pm UTC

Last updated: July 13, 2006 - 4:49 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

I have a table in which a varchar column contains data like shown below:

100
20A
200A
1000
30
200d
35C
400D
500U

etc. I want a query or a function which will return the ascending sorted set like this:

20A
30
35C
100
200d
200A
400D
500U
1000

i.e the numbers should be given preference in ascending or descending sort in normal manner and then uppper case letters like A,B,C... should be given preference for descending sort and lower letters like a,b,c.. for ascending sort. The above data in descending sort would be like this:

1000
500U
400D
200A
200d
100
35C
30
20A

is there a way to achieve this? Please help.

and Tom said...

the way to achieve this would be to store NUMBERS as NUMBERS and STRINGS as STRINGS. You obviously have two fields here -- not one. You should definitely have stored them as TWO fields.


If the fields are always simple like you have above -- some numbers followed by some characters, you can do this:


ops$tkyte@ORA817DEV.US.ORACLE.COM> select x,
2 to_number( substr(x,1,length(x)-nvl(length(replace(translate(x,'0123456789','0000000000'),'0','')),0)) ) n,
3 substr(x,length(x)-nvl(length(replace(translate(x,'0123456789','0000000000'),'0','')),0)+1) s
4 from t
5 order by 2 DESC, 3 DESC
6 /

X N S
---------- ---------- ----------
1000 1000
500U 500 U
400D 400 D
200d 200 d
200A 200 A
100 100
35C 35 C
30 30
20A 20 A

9 rows selected.


Rating

  (15 ratings)

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

Comments

More details required

Sampreetha, February 24, 2003 - 11:45 pm UTC

HI tom,
The solution is very good. But the solution seems to be tricky also. Can you please explain the steps involved in more detail. I am trying to figure it out, but finding it difficult to understand, especially the portion after order by i.e. the details regarding the 2 & 3 in order by clause.



Tom Kyte
February 25, 2003 - 9:20 am UTC

I'm just using standard builtin functions substr, instr, replace and translate?

Just add more columns to the query to see what it is doing:


see what translate(x,'0123456789','0000000000') returns

and then replace(translate(x,'0123456789','0000000000'),'0','')

and so on -- it is just "math"



sorting stupid data

Dan, May 23, 2005 - 9:52 am UTC

Tom,
Thanks for the answer above. I was using it in a couple places until someone reported an "invalid number" error.
99% of my data looks like the original poster's, but there
are a few exceptions that look like 24S3 and PNL1...
Is there any hope of sorting this?
Running oracle 9.2


Tom Kyte
May 23, 2005 - 2:25 pm UTC

well, what do you want to have happen?

if you can express the algorithm, we should be able to use CASE SUBSTR INSTR etc to convert.

A reader, May 23, 2005 - 4:04 pm UTC

CREATE OR REPLACE function my_to_number(p_num in varchar2 ) return number
as
x number;
begin
x := to_number( p_num );
return 1;
exception
when others then return 0;
end;
/

The above function can be used to find which rows are generatings those 'INVALID NUMBERS'

Tom Kyte
May 23, 2005 - 7:14 pm UTC

well, yes -- but in this case they want to sort by a field that is known to be alphanumeric and we need to "parse it", in order to do that we need the rules.

sorting stupid data

Dan, May 23, 2005 - 4:17 pm UTC

I'd like the first part of the sort that grabs the numeric part to only apply to numbers beginning at the first character...
If the number appears later in the string, I'd like it to
get the character-sort treatment that the second half of this sort does.
So with the values 1A, 10B, 24S3, PNL2 respectively, the first sort would pull 1, 10 and 24 and ignore the 3 and 2
that appear after characters.

thanks


Tom Kyte
May 23, 2005 - 7:17 pm UTC

is it just the 26 letters of the alphabet that can signify "stop" or are other characters allowed as well (and I'm assuming 9i? or before)

sorting stupid data

Dan, May 24, 2005 - 9:23 am UTC

yes, just the 26 letters of the alphabet.
oracle 9.2.

Tom Kyte
May 24, 2005 - 10:38 am UTC 

ops$tkyte@ORA9IR2> select c,
  2         to_number(
  3         substr( c, 1,
  4                 decode(instr(translate(c,'ABCDEFGHIJKLMNOPQRSTUVWXYZ',rpad('X',26,'X')),'X'),
  5                        0, length(c),
  6                        instr(translate(c,'ABCDEFGHIJKLMNOPQRSTUVWXYZ',rpad('X',26,'X')),'X')-1 )
  7               )
  8         ) x
  9    from t
 10  /
 
C                   X
---------- ----------
123C2             123
123               123
C123

RE: Complicated sorting required

Neo, June 28, 2005 - 12:50 pm UTC

Hello,

As far as I know, you can configure your Locale in order to achive the type of sorting you require. Perhaps this might help:

</code> http://www.cs.uvm.edu/oracle9doc/server.901/a90236/ch4.htm#1656 <code>

Sorting Data but a little bit different

San_mat, November 09, 2005 - 9:08 am UTC

Hi Tom,
Thank you very much for the explaination.
I have a little bit different scenerio, please look at following >>
We have Product_name as one of the column in the table and & the values in this column are given below when we put an order by clause in a query
------------------------------------------------------------
BBO April 3000 oRO m154 Carde Rechnung Online + Sinus 154 Basic und Card
------------------------------------------------------------------------
-
BBO April 3000 oRO m154 Datae Rechnung Online + Sinus 154 Basic und Data
------------------------------------------------------------------------
--
BBO April 3000 oRO o1540 ohne Rechnung Online + Sinus 154 Basic
------------------------------------------------------------------------
-
BBO April 768 mRO m154 Dataechnung Online + Sinus 154 Basic und Data
------------------------------------------------------------------------
--

And the sorting which is required is like >>
------------------------------------------------------------------------
-
BBO April 768 mRO m154 Dataechnung Online + Sinus 154 Basic und Data
------------------------------------------------------------------------
--
BBO April 3000 oRO m154 Carde Rechnung Online + Sinus 154 Basic und Card
------------------------------------------------------------------------
-
BBO April 3000 oRO m154 Datae Rechnung Online + Sinus 154 Basic und Data
------------------------------------------------------------------------
--
BBO April 3000 oRO o1540 ohne Rechnung Online + Sinus 154 Basic
------------------------------------------------------------------------
Can you please explain how can we do it ?

Tom Kyte
November 11, 2005 - 10:00 am UTC

I don't know what it is you want to do?

sorting stupid data

Dan, November 25, 2005 - 11:59 am UTC

I overlooked this problem with the sort operation you suggested to my May 23/24 post. If you insert a couple rows with '1' and '10' in it, the result sorts like this:

C X
----- ----------
123C2 123
123 123
C123
1 1
10 10

whereas I would like it to go:

C X
----- ----------
1 1
10 10
123C2 123
123 123
C123

basically maintaining the numeric sort when the numeric part starts from the first character, and not throwing an invalid number error when otherwise. I've been fighting with this one for a while!

Thanks for your help.

Tom Kyte
November 25, 2005 - 1:48 pm UTC 

ops$ora10gr2@ORA10GR2> select x, substr( x, 1, length(x)-nvl(length(ltrim(x,'0123456789')),0) )
  2    from t
  3   order by 2;

X                    SUBSTR(X,1,LENGTH(X)
-------------------- --------------------
1                    1
10                   10
123c2                123
123                  123
c123

sorting stupid data

Dan, November 28, 2005 - 8:54 am UTC 

That does work for the data I gave, but I need it to be a numeric sort. If I insert a '2' in there now, I get:
SQL> SELECT   c,
  2           substr (c,
  3                   1,
  4                   LENGTH (c) - nvl (LENGTH (LTRIM (c, '0123456789')), 0))
  5      FROM t
  6  ORDER BY 2;

C     SUBST
----- -----
1     1
10    10
123   123
123C2 123
123   123
2     2
C123

I'd like the 2 to come in between 1 and 10.
It seems that one of the functions is going to have to act like an IF clause.
IF is_numeric(substr(c,1,x)) THEN
  -- apply a to_number sort
...

Thanks again.

sorting stupid data

Dan, November 28, 2005 - 10:02 am UTC

Actually, wrapping this with to_number worked fine.
Sorry to bother you!

Tom Kyte
November 28, 2005 - 1:45 pm UTC

indeed, i missed the mandatory to_number...

Mirjana, June 22, 2006 - 4:51 am UTC

Hi Tom,
I also have some complicated sorting to do, and I would appreciate any help on this.
How do you sort something like this:

CREATE TABLE PROVA(TXT VARCHAR2(100));

INSERT INTO TABLE PROVA('1.1.1');
INSERT INTO TABLE PROVA('12.1.1');
INSERT INTO TABLE PROVA('2.1.1');
INSERT INTO TABLE PROVA('2.1.1.3');
INSERT INTO TABLE PROVA('9.1');

The result set should be ordered like this:

SELECT TXT FROM PROVA ORDER BY something

1.1.1
2.1.1
2.1.1.3
9.1
12.1.1

Thanks!

Tom Kyte
June 22, 2006 - 12:30 pm UTC 

always cracks me up.

please sort like this:

(picture of some data)


without "here are the RULES by which we sort by"

I'm guessing:

parse the string, turning each '.' separated number into a number and order by them.


ops$tkyte@ORA10GR2> select txt,
  2         to_number( substr( '.'||txt||'.', instr('.'||txt||'.','.',1,1)+1, instr('.'||txt||'.','.',1,2)-instr('.'||txt||'.','.',1,1)-1 ) ) c1,
  3         to_number( substr( '.'||txt||'.', instr('.'||txt||'.','.',1,2)+1, instr('.'||txt||'.','.',1,3)-instr('.'||txt||'.','.',1,2)-1 ) ) c2,
  4         to_number( substr( '.'||txt||'.', instr('.'||txt||'.','.',1,3)+1, instr('.'||txt||'.','.',1,4)-instr('.'||txt||'.','.',1,3)-1 ) ) c3,
  5         to_number( substr( '.'||txt||'.', instr('.'||txt||'.','.',1,4)+1, instr('.'||txt||'.','.',1,5)-instr('.'||txt||'.','.',1,4)-1 ) ) c4,
  6         to_number( substr( '.'||txt||'.', instr('.'||txt||'.','.',1,5)+1, instr('.'||txt||'.','.',1,6)-instr('.'||txt||'.','.',1,5)-1 ) ) c5
  7    from prova
  8    order by c1, c2, c3, c4, c5
  9  /

TXT                          C1         C2         C3         C4         C5
-------------------- ---------- ---------- ---------- ---------- ----------
1.1.1                         1          1          1
2.1.1.3                       2          1          1          3
2.1.1                         2          1          1
9.1                           9          1
12.1.1                       12          1          1

(you don't have to select ths to_number()... you can just order by the to_number()....

Order by column alias in prova example

coop, June 22, 2006 - 5:41 pm UTC

The prova example above works as advertised for me in Oracle 92. The use of column aliases c1-c5 in the order by caught me off guard -- is this a 10g feature, or just an oddity?

For example, you can not use c2 in a group by or where clause.

Tom Kyte
June 23, 2006 - 10:07 am UTC 

ops$tkyte@ORA9IR2> select txt,
  2         to_number( substr( '.'||txt||'.', instr('.'||txt||'.','.',1,1)+1, instr('.'||txt||'.','.',1,2)-instr('.'||txt||'.','.',1,1)-1 ) ) c1,
  3         to_number( substr( '.'||txt||'.', instr('.'||txt||'.','.',1,2)+1, instr('.'||txt||'.','.',1,3)-instr('.'||txt||'.','.',1,2)-1 ) ) c2,
  4         to_number( substr( '.'||txt||'.', instr('.'||txt||'.','.',1,3)+1, instr('.'||txt||'.','.',1,4)-instr('.'||txt||'.','.',1,3)-1 ) ) c3,
  5         to_number( substr( '.'||txt||'.', instr('.'||txt||'.','.',1,4)+1, instr('.'||txt||'.','.',1,5)-instr('.'||txt||'.','.',1,4)-1 ) ) c4,
  6         to_number( substr( '.'||txt||'.', instr('.'||txt||'.','.',1,5)+1, instr('.'||txt||'.','.',1,6)-instr('.'||txt||'.','.',1,5)-1 ) ) c5
  7    from prova
  8    order by c1, c2, c3, c4, c5
  9  /

TXT
-------------------------------------------------------------------------------
        C1         C2         C3         C4         C5
---------- ---------- ---------- ---------- ----------
1.1.1
         1          1          1

2.1.1.3
         2          1          1          3

2.1.1
         2          1          1

9.1
         9          1

12.1.1
        12          1          1


ops$tkyte@ORA9IR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE    9.2.0.6.0       Production
TNS for Linux: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production



it is documented as being usable in the order_by_clause in the SELECT statement.

Thanks!!!

Mirjana, June 23, 2006 - 4:33 am UTC

Perfect.
Thank you for your help!

Best regards,
Mirjana

Dictionary Sort

Suzanne, July 12, 2006 - 8:37 pm UTC

I have a field that is all over the map and my developers would like to present the text in "dictionary order" by that they mean that they would like it to come out as - 1,2,3,4,5,6,7,8,9,10,11,12 ... 20,21,22 ... 30,31 ... 100, 101 ... A,B,C,D. There is no pattern to the data and the field can contain all numbers, part numbers/part characters or all characters. Here is an example -
VENDOR_ID
---------------
27770
27777
27808
27981
28001
28188
28221
28338
28367
28385
28493

VENDOR_ID
---------------
234
amanda
intrastate
dfdgd
TJTLOKY
TJTSHWI
TJTSTKY
TJTWEFL
TKTBOND
TKTDCMN
TKTMAWI

Is there any way to do this?

Tom Kyte
July 12, 2006 - 9:04 pm UTC

do you mean something like this?

create or replace function my2num( p_str in varchar2 ) return number
as
begin
return to_number(p_str);
exception
when others then return null;
end;
/

select *
from t
order by my2num(x), x;


of course, you can skip the my2num if your "numbers" are easy enough to identify, eg:


select *
from t
order by
decode( replace( translate(x,'0123456789','0000000000'), '0', ''), null,
to_number(x), to_number(null) ), x;


if your numbers are all just digits (no spaces, no '.', etc)




Dictionary Sort

Suzanne, July 13, 2006 - 1:44 pm UTC

Thank you. Both options work quite well. I've given my developer the choice of which solution he likes the best. The function (my2num) has the extra, added elegance of being much easier to insert into an existing SELECT.

Tom Kyte
July 13, 2006 - 4:49 pm UTC

but my2num won't perform as well

but my2num won't do the wrong thing if you get 192.168.0.1 either



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 channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.