sort the alpha numeric value

Hi all,

   I am using foxpro db!

   I have a string field in a table. But the value in this field will be in the alphanumeric format.
For e.g., String123, String23, Strinf150, String30, and String1111. Now if I fetched the data by sorting
on this field, the data is written as, String1111, String123, Strinf150, String23, and String30. but
I need the output in the following order,
String23, String30, String123, Strinf150, and String1111

i.e., if the data is compared in the alphanumeric format I could got the expected result but it is comparing
as a string. Is there any command to specify to sort the values based on alpha numeric instead of just
a string?

    I had posted the same question in the Foxpro topic, I got some comments "
"select * from test order by val(substring(chkColumn,7))"

  but if I execute this statement, it gives "Column " is not found" error message.

  but a statement like this with appropriat function works in the MS-SQL enviroment.

  I will be very thankful for your answers!

siva
LVL 26
ksivananthAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
dmkoelleConnect With a Mentor Commented:
I have written a Java comparator that does exactly what you're looking for.  See http://www.davekoelle.com/usability/alphanum.html

If you're looking for a "select" statement instead of Java code, I recommend asking your question in a different forum.

Hope this helps!
Dave
0
 
bobbit31Commented:
have you tried:

select substring(chkColumn, 7) as orderCol, * from test order by orderCol
0
 
bobbit31Commented:
or

select substring(chkColumn, 7) as orderCol, * from test order by val(orderCol)
0
 
ksivananthAuthor Commented:
Hi dmkoelle,

   Really the comparator class was very usefull for me. I thought it will be very slow in performance so I avoided intially. Then I did put a try, I couldn't find that much differences in performance.

   In your code, to check the digit you need not use that algorithm because it is already available in the Java's Character class. Anyway thanks a lot!

with cheers
siva
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.