SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    The Hiding One lynlimz's Avatar
    Join Date
    Jul 2000
    Location
    Singapore
    Posts
    2,103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Sorting Alphanumeric+dots

    Hi,

    I'm try to sort a column in mysql db that contains numbers, letters and dots.

    Eg.

    1.4.1a
    1.2.3
    1.2.30
    1.4.5

    It should sort to:
    1.4.5
    1.4.1a
    1.2.30
    1.2.3

    How do i do that? A normal ORDER BY would not work with the .30 and .3
    The column type is VARCHAR. I tried looking at the mysql doc, and some user comments suggested SOUNDEX and ORD but it doens't seem to work for me. Any ideas?

    Thanks.
    "Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
    -- Albert Einstein

  2. #2
    The Hiding One lynlimz's Avatar
    Join Date
    Jul 2000
    Location
    Singapore
    Posts
    2,103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Any ideas guys? Thanks
    "Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
    -- Albert Einstein

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you could, i'm sure, write a series of nested functions that use LOCATE to find the periods and SUBSTR to pull out whatever's between two of them...

    ... but it would be quite nasty

    if i were doing it, i'd just define three extra columns in the table, and put the sort values in there every time i add a new row


    rudy


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
  •