Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Location
    Iceland
    Posts
    6

    Question Unanswered: Sorting strings and numbers in SQL quiery

    I have a problem with sorting by a string-column when i use SQL quiery on SQLServer. There are both numbers, strings and mixture of both in this column and when i sort it it looks very bad. F.ex. 100 6 600 84 9FT RUBB. I would like to sort numbers numerically and both strings and mixed alphabetically, 6 84 100 600 9FT RUBB. Do you know if this is possible??

  2. #2
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53
    hi margrea ,

    either include a calculated column to sort on
    or build up the order on the fly:


    :
    order by
    '0000000000'+rtrim(field)



    markus
    Last edited by msieben; 05-20-03 at 11:15.

  3. #3
    Join Date
    May 2003
    Location
    Iceland
    Posts
    6
    Thanks Markus for your reply

    What is "'0000000000'+rtrim(field)" supposed to do? I still get the same alphabetical order on the numbers (i.e. 100 6 600 ...)

  4. #4
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53
    Originally posted by margrea
    Thanks Markus for your reply

    What is "'0000000000'+rtrim(field)" supposed to do? I still get the same alphabetical order on the numbers (i.e. 100 6 600 ...)
    this will build up a string for the numbers with leading zeros, so they will get sorted correctly ( pls replace the "field" with your mixed char/numeric column). you might need more zero when your column holds longer strings...

    the sort-column should hold something like

    000000000100
    000000000006
    000000000600
    ABC
    :




    markus

  5. #5
    Join Date
    May 2003
    Location
    Iceland
    Posts
    6

    Talking

    OK thanx alot this is working for the numbers:

    right('00000000000000000000'+rtrim(ProjectID),20)

    ..I have a nvarchar(20)

    Happy happy joy! Finally I can continue

Posting Permissions

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