www.webdeveloper.com
Results 1 to 12 of 12

Thread: Sorting on an alphanumeric field

  1. #1
    Join Date
    Aug 2004
    Posts
    3

    Sorting on an alphanumeric field

    One of the fields in a table I have is alphanumeric. THe values will be something like this:

    MM8
    MM9
    MM10
    MM11
    ...

    Is there a way for me to sort on that field within sql, or do I need to extract the data into a perl array and futz with it there?

    Obviously, I don't want the sort to look like this:

    MM1
    MM10
    MM11
    ...
    MM19
    MM2
    MM20
    ...

    Thanks for any help.

  2. #2
    Join Date
    Jul 2006
    Location
    Nebraska
    Posts
    380
    I had a similar problem a ways back, I never found a good way to sort it.
    I ended up writing a little program in PHP to sort it, but thats long gone.

  3. #3
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    your best bet would be to separate the two items, the letters then the #'s in your case since there's a clear separation. you would then have as your order by:

    ORDER BY alpha_column, number_column

  4. #4
    Join Date
    Aug 2004
    Posts
    3
    Do you mean create two fields out of the one within the table?

  5. #5
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    yes.

  6. #6
    Join Date
    Feb 2003
    Posts
    2,745
    what db r u using? what u want isn't too tricky in some, more difficult in others

  7. #7
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    the only thing i can think of, other than what i suggested, is using substring() to break it, but you would need to know how many letters. not sure that we know that here. it might make more sense to have them as 2 separate fields. what are you thinking russell?

  8. #8
    Join Date
    Feb 2003
    Posts
    2,745
    was thinking substring and charindex with either an ascii check or isnumeric() if db is mssql.

    msaccess queries can use pretty much all of the string functions of vbscript. Oracle and MySQL (as u know) I'm not familiar enough to attempt in SQL so I'd break into 2 fields as u suggest, or do in client code.

  9. #9
    Join Date
    Feb 2003
    Posts
    2,745
    we're making this too hard.

    If it is ALWAYS MM###

    then the query is easy

    assuming the table is called myTable and the field is called v
    Code:
    SELECT 	Cast(Right(v, len(v)-2) as int) as num
    FROM	myTable
    Order By
    	num
    that's MSSQL code. There are equivalent casting functions in all dbs

  10. #10
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    i don't think mysql has a cast function...and i don't think it's always MM### i think it's A+#+ (one or more characters, one or more numbers) at least that's my impression.

  11. #11
    Join Date
    Aug 2004
    Posts
    3
    Last night before I went to bed (and I go to bed a lot earlier than you guys) I modified my application and added an extra numeric sorting field to the table. It strips out the alpha characters and stuffs the remaining numeric into that field. Then I sort on the numeric field.

    However, that's some interesting stuff that you posted, and I'd like to play with it a little. Thanks for the help!

  12. #12
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    to satisfy my curiosity, is it A+#+ or MM##?

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles