MySQL Forums
Forum List  »  Knowledge Base

how to order alphanumerical fields?
Posted by: Maria Pascual
Date: August 27, 2009 04:38AM

Hello all,


Anyone knows how to order fields with alphanumerical values? I have a table with the field Position. This field has values like 1. or 1.1. or 2.1.5.

I want to do a select of the rows of the table and organize them by this field, so I get:

1.
1.1.
1.1.1.2.
1.1.10.
1.1.11.
1.1.12

However I get:

1.
1.1.
1.1.10.
1.1.11.
1.1.12.
1.1.1.2.

The query I do is:

"SELECT idChapter, Name, ModificationDate, cast(RPAD(replace(`Position`,\".\",\"\"),15,0) as SIGNED ) RP FROM Chapters where idBook=1
ORDER BY RP";

This is the closest approach I have got to what I want, but it fails if there are numbers>9 :(

I have been working on it for a long time and look for solutions through internet but nothing has work.

Can anybody help me, please?

Options: ReplyQuote


Subject
Views
Written By
Posted
how to order alphanumerical fields?
2718
August 27, 2009 04:38AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.