I'd use a cursor loop, and parse through the string extracting the numeric
characters, convert the numeric characters to a Int to be used as a
sequence number. Insert the sequence number and the string into a temp
table, (or add seqnum to the current table) and
select from X order by seqnum.
I won't say anymore,
Mike, the crazy null guy!
> Hi Rich,
> I believe that since they are definitely considered as strings they are
> sorted as such,
> how can you pretend that '#2 NOV' is considered as a number?
> If you cannot define a rule in the structure of the data it is
> impossible to sort, not only in sql, but in life!
> IF we take for granted that values start from first or second position
> ('#') you can use something like this:
>
> SELECT setname from sets order by CASE SUBSTRING(setname,1,1) WHEN '#'
> THEN SUBSTRING(setname,2) ELSE setname END;
>
>
> Cheers
> Claudio
>
>
> Richard Gagnon wrote:
>> 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
>>
>>
>>
>>
>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=mich...@j3ksolutions.com
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
