Professional Web Applications Themes

how to sort numbers in varchar2 - Oracle Server

I have a varchar2 column which stores characters as well as numbers in string such as '12345'. I would like to sort the column so that it orders by the actual number in the number string. However, if I do a simple order by, 111 is smaller than 22. Is there a easy way to accomplish this in sql instead of doing in programming? Thanks, Susan...

  1. #1

    Default how to sort numbers in varchar2

    I have a varchar2 column which stores characters as well as numbers in
    string such as '12345'. I would like to sort the column so that it
    orders by the actual number in the number string. However, if I do a
    simple order by, 111 is smaller than 22. Is there a easy way to
    accomplish this in sql instead of doing in programming?

    Thanks,
    Susan
    Susan Lam Guest

  2. #2

    Default Re: how to sort numbers in varchar2

    select lpad(your_column,max_size)
    from your_table
    order by 1

    "Susan Lam" <susana73hotmail.com> wrote in message
    news:7186ed56.0307071524.17448526posting.google.c om...
    > I have a varchar2 column which stores characters as well as numbers in
    > string such as '12345'. I would like to sort the column so that it
    > orders by the actual number in the number string. However, if I do a
    > simple order by, 111 is smaller than 22. Is there a easy way to
    > accomplish this in sql instead of doing in programming?
    >
    > Thanks,
    > Susan

    PQ Guest

  3. #3

    Default Re: how to sort numbers in varchar2

    don't store numbers in character columns, store them in numeric columns.
    Jim

    "Susan Lam" <susana73hotmail.com> wrote in message
    news:7186ed56.0307071524.17448526posting.google.c om...
    > I have a varchar2 column which stores characters as well as numbers in
    > string such as '12345'. I would like to sort the column so that it
    > orders by the actual number in the number string. However, if I do a
    > simple order by, 111 is smaller than 22. Is there a easy way to
    > accomplish this in sql instead of doing in programming?
    >
    > Thanks,
    > Susan

    Jim Kennedy Guest

  4. #4

    Default Re: how to sort numbers in varchar2

    If you have just positive numbers, you can use LPAD/RPAD for having
    equal length for all characters.
    if you have mixture (positive and negative) - then there is no simple
    solution.
    Jan


    "Jim Kennedy" <kennedy-down_with_spammerscomcast.net> wrote in message news:<9tqOa.2065$wk6.1582rwcrnsc52.ops.asp.att.ne t>...
    > don't store numbers in character columns, store them in numeric columns.
    > Jim
    >
    > "Susan Lam" <susana73hotmail.com> wrote in message
    > news:7186ed56.0307071524.17448526posting.google.c om...
    > > I have a varchar2 column which stores characters as well as numbers in
    > > string such as '12345'. I would like to sort the column so that it
    > > orders by the actual number in the number string. However, if I do a
    > > simple order by, 111 is smaller than 22. Is there a easy way to
    > > accomplish this in sql instead of doing in programming?
    > >
    > > Thanks,
    > > Susan
    Jan Guest

  5. #5

    Default Re: how to sort numbers in varchar2

    I tricked the system into doing this for me by ordering by the size of
    the column and then by the data in the column. For instance, assume I
    have TABLE_A as follows:

    COL_A
    --------------------
    123
    45
    532
    133

    Now, I can "order" these by doing the following:

    ORA9I SQL> select col_a,length(col_a)
    2 from table_a
    3 order by length(col_a),col_a;

    COL_A LENGTH(COL_A)
    -------------------- -------------
    45 2
    123 3
    133 3
    532 3

    Then, I just never displayed the second column in my application.

    Of course, doing the proper thing and storing the numbers in a number
    field would be better....

    HTH,
    Brian

    Susan Lam wrote:
    >
    > I have a varchar2 column which stores characters as well as numbers in
    > string such as '12345'. I would like to sort the column so that it
    > orders by the actual number in the number string. However, if I do a
    > simple order by, 111 is smaller than 22. Is there a easy way to
    > accomplish this in sql instead of doing in programming?
    >
    > Thanks,
    > Susan
    --
    ================================================== =================

    Brian Peasland
    [email]oracle_dbaremove_spam.peasland.com[/email]

    Remove the "remove_spam." from the email address to email me.


    "I can give it to you cheap, quick, and good. Now pick two out of
    the three"
    Brian Peasland Guest

  6. #6

    Default Re: how to sort numbers in varchar2

    On Tue, 8 Jul 2003 13:09:46 GMT, Brian Peasland
    <oracle_dbaremove_spam.peasland.com> wrote:
    >I tricked the system into doing this for me by ordering by the size of
    >the column and then by the data in the column. For instance, assume I
    >have TABLE_A as follows:
    >
    >COL_A
    >--------------------
    >123
    >45
    >532
    >133
    >
    >Now, I can "order" these by doing the following:
    >
    >ORA9I SQL> select col_a,length(col_a)
    > 2 from table_a
    > 3 order by length(col_a),col_a;
    >
    >COL_A LENGTH(COL_A)
    >-------------------- -------------
    >45 2
    >123 3
    >133 3
    >532 3
    >
    >Then, I just never displayed the second column in my application.
    >
    >Of course, doing the proper thing and storing the numbers in a number
    >field would be better....
    Your solution has one drawback : It can only handle integers. Instead,
    just :

    select col_a from table_a order by to_number(col_a);

    handles decimals as well.


    - Kenneth Koenraadt
    Kenneth Koenraadt Guest

  7. #7

    Default Re: how to sort numbers in varchar2

    Yours has also one big drawback,

    Original problem was:

    "I have a varchar2 column which stores characters as well as numbers in
    string such as '12345'. "

    so if you do TO_NUMBER(col1) and there is a text it will raise an error.


    Kenneth Koenraadt wrote in message news:<3f0ad068.448094news.inet.tele.dk>...
    > On Tue, 8 Jul 2003 13:09:46 GMT, Brian Peasland
    > <oracle_dbaremove_spam.peasland.com> wrote:
    >
    > >I tricked the system into doing this for me by ordering by the size of
    > >the column and then by the data in the column. For instance, assume I
    > >have TABLE_A as follows:
    > >
    > >COL_A
    > >--------------------
    > >123
    > >45
    > >532
    > >133
    > >
    > >Now, I can "order" these by doing the following:
    > >
    > >ORA9I SQL> select col_a,length(col_a)
    > > 2 from table_a
    > > 3 order by length(col_a),col_a;
    > >
    > >COL_A LENGTH(COL_A)
    > >-------------------- -------------
    > >45 2
    > >123 3
    > >133 3
    > >532 3
    > >
    > >Then, I just never displayed the second column in my application.
    > >
    > >Of course, doing the proper thing and storing the numbers in a number
    > >field would be better....
    >
    > Your solution has one drawback : It can only handle integers. Instead,
    > just :
    >
    > select col_a from table_a order by to_number(col_a);
    >
    > handles decimals as well.
    >
    >
    > - Kenneth Koenraadt
    Jan Guest

Similar Threads

  1. memory sort and disk sort
    By dusong in forum Informix
    Replies: 4
    Last Post: October 24th, 02:11 PM
  2. Replies: 1
    Last Post: September 9th, 07:16 AM
  3. sql SORT order not working on numbers?
    By Alpay Eno in forum ASP Database
    Replies: 2
    Last Post: July 10th, 03:47 PM
  4. Replies: 3
    Last Post: July 9th, 10:25 AM
  5. select only number from varchar2
    By in forum Oracle Server
    Replies: 1
    Last Post: January 5th, 04:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

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 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139