Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Location
    Guadalajara
    Posts
    9

    Unanswered: Order varchars like number

    Hi everyone.
    I have a table that contains documents, each documen have a ID.
    This ID is like this: 1.1, 1.2, 1.3.1, and so ...
    But, if i make a query and i say that query must be ordered by ID,
    it give a result like this:

    1.1
    1.11
    1.12
    1.13
    1.14
    1.15
    1.2
    1.3
    1.4

    If I add a zero, the result is in order:

    01.01
    01.02
    01.03
    01.04
    01.11
    01.12
    01.13
    01.14
    01.15

    But, the question is:

    Is there another way to sort this varchar ID correctly without adding a zero to it ?


    Thanks !!!

  2. #2
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Order varchars like number

    Order By replace(id,'.','')


    should ids place this order

    1.1
    1.2
    1.3
    1.4
    1.11
    1.12
    1.13
    1.14
    1.15

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    What about this?

    order by cast(field as numeric)

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You need to add the zero. Write a function that will do it for you, and then you can reference it easily in queries.

    blindman

Posting Permissions

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