SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Order by # Characters (Database Query)

    I know how to use the ORDER BY clause to display data from a database table in a certain order, as dictated by a particular field. But is there a way to order the display according to the number of characters?

    It would help me implement my linkify script - http://www.sitepoint.com/forums/showthread.php?t=485677 - if I could order various terms from the longest (e.g. South_Pole) to shortest (e.g. South).

    Thanks.

  2. #2
    SitePoint Addict
    Join Date
    Jul 2007
    Location
    San Jose, California
    Posts
    355
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This seems to be more of a SQL; but you could do it in php have all the data stored in an array, modify a bubble sort or prefered sorting alogrythm so that it sorts on strlegn() and no on numeric value.


    BUt in mysql do it like this


    SELECT * FROM TABLENAME order by char_Length(TUPLENAME);

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i'm sure there's a function in mysql that will count the number of characters in a string. let's see, where would i find such a function?

    hmmm.... i think the "String Functions" page would have it: http://dev.mysql.com/doc/refman/5.0/...functions.html

  4. #4
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    i'm sure there's a function in mysql that will count the number of characters in a string. let's see, where would i find such a function?

    hmmm.... i think the "String Functions" page would have it: http://dev.mysql.com/doc/refman/5.0/...functions.html
    That's perfect. But how do you REVERSE the order? The following doesn't work:

    PHP Code:
    $Word mysql_query ("SELECT Name Term, Plural
    FROM g_glossary
    GROUP BY Name
    ORDER BY REVERSE(CHARACTER_LENGTH(Name))"
    ) or die (mysql_error()); 
    It looks like they're inserting REVERSE in the SELECT CLAUSE in the Manual, but that doesn't work for me, either, nor does it make sense.

    Thanks.

  5. #5
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You're right, that doesn't make sense

    Code:
    ORDER BY CHAR_LENGTH(Name) DESC

  6. #6
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, I should have remembered from my JavaScript table-column sort scripts. Thanks.


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
  •