Professional Web Applications Themes

Any way of ordering text by its numerical values? - ASP Database

Hello, When I have a record set drawn from a text column in the database and I do a order by, I get this: 17 18 19 2 20 Is there a way of taking numbers stored in a text column and getting them to format in numerical order, like this: 2 17 18 19 20 ??? Thanks, Mike...

  1. #1

    Default Any way of ordering text by its numerical values?

    Hello,

    When I have a record set drawn from a text column in the database and
    I do a order by, I get this:

    17
    18
    19
    2
    20

    Is there a way of taking numbers stored in a text column and getting
    them to format in numerical order, like this:

    2
    17
    18
    19
    20

    ???

    Thanks,
    Mike
    Mike Guest

  2. #2

    Default Re: Any way of ordering text by its numerical values?

    Mike wrote on 02 sep 2004 in microsoft.public.inetserver.asp.db:
     

    solution 1
    Do not use a text field but a number field.

    solution 2
    Copy the textfield column to a number field column

    solution 3
    extract whole column to an array and convert and sort with ASP
    [bad solution for bigger databases]


    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress,
    but let us keep the discussions in the newsgroup)

    Evertjan. Guest

  3. #3

    Default Re: Any way of ordering text by its numerical values?

    "Evertjan." <net> wrote in message
    news:109.133.29... 
    >
    > solution 1
    > Do not use a text field but a number field.
    > solution 2
    > Copy the textfield column to a number field column
    > solution 3
    > extract whole column to an array and convert and sort with ASP
    > [bad solution for bigger databases][/ref]

    Solution 4
    Use the conversion function your database provides (for Oracle it's
    to_number()) within the order by clause. Like this: select x from y
    order by to_number(x)


    Kris Guest

  4. #4

    Default Re: Any way of ordering text by its numerical values?

    "Kris Eiben" <com> wrote in message news:<phx.gbl>... 
    > >
    > > solution 1
    > > Do not use a text field but a number field.
    > > solution 2
    > > Copy the textfield column to a number field column[/ref][/ref]

    How do you do this in a select statement?
     [/ref]

    What function in ASP can you use to sort an array?
     

    I looked at the jetsql reference (I am using Access) but I don't see a
    function like to_number(). Does jetSQL have one?
    Mike Guest

  5. #5

    Default Re: Any way of ordering text by its numerical values?


    "Mike" <com> wrote in message
    news:google.com... 


    sSQL = "select yourNumericTextField from yourTable order by
    CInt(yourNumericTextField)"

    Note that if you have a text field that when converted to numeric is greater
    than 32,767 or less than -32,768, you'll get an overflow error. In that
    case, you'd want to CLng your data instead of CInt'ing it.

    If you have decimal data, you'd use CSng or CDbl. These all have
    limitations:
    http://www.devguru.com/Technologies/vbscript/quickref/CInt.html

    Ray at home


    Ray Guest

  6. #6

    Default Re: Any way of ordering text by its numerical values?

    Mike wrote on 03 sep 2004 in microsoft.public.inetserver.asp.db:
     [/ref]
    >
    > What function in ASP can you use to sort an array?[/ref]

    You don't, ASP is not a language. [Sorry for the pun]

    You can [yes serverside] use jscript sort()
    even specifying your own sort function

    <http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=89>

    or
    build your own sort routine in ASP vbscript or jscript.

    <http://developer.netscape.com/viewsource/kuslich_ssjs2.htm>

    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress,
    but let us keep the discussions in the newsgroup)

    Evertjan. Guest

Similar Threads

  1. Values in Text Boxes
    By pinball4200 in forum Coldfusion - Getting Started
    Replies: 3
    Last Post: October 13th, 09:07 PM
  2. convert numerical value to text?
    By Nathon Jones in forum Macromedia Contribute Connection Administrtion
    Replies: 3
    Last Post: March 8th, 10:31 AM
  3. Export numerical luminosity values?
    By Tim_Brazelton@adobeforums.com in forum Adobe Photoshop Mac CS, CS2 & CS3
    Replies: 1
    Last Post: March 2nd, 08:13 PM
  4. displaying values in a text box
    By Bill in forum ASP
    Replies: 1
    Last Post: September 9th, 09:53 PM
  5. Numerical Order
    By iMonkey in forum ASP.NET General
    Replies: 0
    Last Post: July 31st, 02:43 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