List:General Discussion« Previous MessageNext Message »
From:Richard Gagnon Date:March 20 2009 7:03pm
Subject:MySQL General Discussion question
View as plain text  
Sorting a varchar field alphabetically with correct numerical order help
needed

 

I have  a varchar 50 field that contains product names, which are typically
numerical, alphabetical and punctuation thrown in. I would like to have them
returned in some sort of order that is roughly alphabetical, but with the
numbers in numerical order. The basic Order By clause does not do it
correctly.

An example is:

 

SELECT setname  FROM sets ORDER BY setname

 

Sample values of setname are:
658
#1 JCAL
011
#2 NOV
#11 NOV
#12 NOV
985

ABC

#123 NOV

The results I get are:
#1 JCAL
#11 NOV
#12 NOV

#123 NOV
#2 NOV       <<<<<<<<< wrong
011
658
985

ABC

 

The results I want are:

#1 JCAL
#2 NOV     <<<<<<<<< should be here
#11 NOV
#12 NOV

#123 NOV
011
658
985

ABC


In the above, the #2 JCAL should be second, otherwise, list is correct. I
could also live with the values beginning with # or any alpha character
coming after the numerical ones, but the 1, 11, 2, order is the issue.

 

Does anyone have any idea how to do this? I have been playing around with
various suggested ways, including casting and converting, but so far have
not been able to solve this. Any ideas would be greatly appreciated.

 

Thanks, Rich

 


Thread
MySQL General Discussion questionRichard Gagnon20 Mar
  • Re: MySQL General Discussion questionClaudio Nanni20 Mar
    • Re: MySQL General Discussion questionmichael21 Mar
  • Re: MySQL General Discussion questionJoerg Bruehe21 Mar