#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    1
    Rep Power
    0

    SORTING '1 of 20% item' Firebird2.1 SQL


    I have a Firebird2.1 table that stores a custom item - a varchar(35) column named 'PARTICULARS' and it is something like this:

    1. 1 of 20%item
    2. 5 of 20%item
    3. 3 of 20%item
    4. 4 of 20%item
    5. 7 of 20%item
    6. 2 of 20%item
    7. .
    8. .
    9. .


    and I wanted to sort them in the natural way like this:

    1. 1 of 20% item
    2. 2 of 20% item
    3. 3 of 20% item
    4. 4 of 20% item
    5. 5 of 20% item
    6. 7 of 20% item
    7. .
    8. .
    9. .


    I have done numerous sql commands like:

    1. select * from TABLE order by 1
    2. select * from TABLE order by PARTICULARS asc nulls last
    3. select * from TABLE order by '00000000000000000000000000000000000'+trim(PARTICULARS)(35)
    4. select * cast(PARTICULARS as varchar(35)) from TABLE order by 1


    But still I get the same table above. I am a newbie vb.net programmer and in firebird sql and it had been a couple of days since I started working this stuff. Would someone help me. Thank you in advance.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2008
    Posts
    4
    Rep Power
    0
    Try

    SELECT SUBSTRING(PARTICULARS FROM 1 FOR position(' of ' in PARTICULARS)),
    a.* FROM TABLE a ORDER BY 1
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2007
    Posts
    36
    Rep Power
    8
    Try this command:

    select * from <YourTableHere> order by particulars [asc | desc] nulls [first | last]

IMN logo majestic logo threadwatch logo seochat tools logo