Thread: Sorting strings in Alphanumeric order

1. Registered User
Join Date
May 2006
Posts
10

Unanswered: Sorting strings in Alphanumeric order

Hi people,

I have a database Oracle and i have a table called RELATORIO and this table has a column named NOME. I need to retrieve all objects from table RELATORIO in order.

Suppose I have: A 1, A 10, A 3, A 2 . I would like to retrieve the objects in the following order A 1, A 2, A 3, A 10.

But today when a execute a SELECT plus Order By I get A 1, A 10, A 2, A 3

How I can solve this problem? Can I use NLS-SORT? How?

thanks

2. Registered User
Join Date
Sep 2004
Location
London, UK
Posts
565
You'll have to tell it what you know and it doesn't - that you want to sort by the first space-separated word, then by the second word treated as a number. Use SUBSTR and INSTR to get the separate elements. For the second word, either use TO_NUMBER or LPAD.

3. Registered User
Join Date
Jun 2004
Location
Liverpool, NY USA
Posts
2,519
In other words

Code:
```select nome
from RELATORIO
order by substr(none,1,instr(none,' ',1)-1),
to_number(trim(substr(none,instr(none,' '))));```
This code assumes that nome ALWAYS follows the pattern of an alpha string followed by a blank, followed by a number.

4. Registered User
Join Date
May 2006
Posts
10

Sorting strings in Alphanumeric orde

The solutions suggested by my friends don't solve for generic names. I give one example, but I need the solution be generic.

It is possible configurate a new attribute to be used with NLS_SORT?

5. Registered User
Join Date
Aug 2004
Location
France
Posts
754
I don't think NLS_SORT could help you here, and I don't see how you would get a generic solution here : your numbers are inside a string, so they will be sorted as strings unless they are converted to numbers (hence WilliamR and beilstwh's proposition).

Maybe it's a design issue : shouldn't the first letter be stored in a different column from the following number ?

col1 col2
A 1
A 10
...

In this case col2 would be a number and would be sorted as such, not as a string .

HTH & Regards,

rbaraer

6. Registered User
Join Date
May 2006
Posts
10
I have a java graphic interface and in it the users can create the object relatorio. This object has a lot of attributes among them we have "nome".
The nome attribute can assume different alphanumeric values, because the user give the name it want. After created the objetc relatorio it is stored in a database using Interbase.

There is another interface that allows users view the relatorios created before. I use a SQL (select + order by) to retrieve this datas. The order should consider number. I cann't break the variable name and put it in another column. This is not efficient.

7. Registered User
Join Date
May 2004
Location
BA [ARG]
Posts
137
Originally Posted by Aloizio
There is another interface that allows users view the relatorios created before. I use a SQL (select + order by) to retrieve this datas. The order should consider number. I cann't break the variable name and put it in another column. This is not efficient.
I don't know much about your system but having two columns as your primary key is efficient, even better if one or both are numeric.
I'd definitely split the column.

There's nothing you can do with NLS_SORT since the column is alphanumeric, not numeric.
You'll have to work with beilstwh suggestion or something similar.

8. Registered User
Join Date
May 2004
Location
Dominican Republic
Posts
721
Care to let us know _at least_ the rules behind this game (you know, what can and not be possible) ?

9. Registered User
Join Date
Jun 2004
Location
Liverpool, NY USA
Posts
2,519
I agree with DKG, I have been programming for 25 years and I have found that it is much more efficent to have data broken up into different columns then to have to parse a single column every time it is used. However, if the nome column is a single identifer where the component parts don't mean anything, then leave it together.

10. Registered User
Join Date
May 2006
Posts
10

Sorting strings in Alphanumeric order

I have already solved the problem in the application level but I need to solve in the Oracle side when a execute a SELECT query.

And I agreen with RBARAER that NLS_SORT won't be solve this problem.

I can not create another column because I can have for example one string like ab32c12xxx3. In this case should I have six column?

11. Registered User
Join Date
Jun 2004
Location
Liverpool, NY USA
Posts
2,519
You do not understand what people have been asking you!!! If you have the following strings

ab32c12xxx3
A 1
A 3
A 10
el/mfdo489430j

WHAT are your rules to sort them. We can't suggest code unless you tell us your complete rules to sort any string your users can enter. We suggested fixes because you only showed us examples that started with a single letter followed by a numeric string. If it truly can be anything, what are the sorting rules???

12. Registered User
Join Date
Sep 2004
Location
London, UK
Posts
565
Originally Posted by Aloizio
I have already solved the problem in the application level but I need to solve in the Oracle side when a execute a SELECT query.

And I agree with RBARAER that NLS_SORT won't be solve this problem.

I can not create another column because I can have for example one string like ab32c12xxx3. In this case should I have six column?
Now you've completely lost me. "ab32c12xxx3" would be one column, wouldn't it, because it's one word? I assumed the spaces indicated word boundaries, in which case perhaps you could use REGEXP_REPLACE to left-pad each word with zeroes, but evidently there are more rules to this than I first thought.

NLS_SORT and NLS_COMP are not limited to numeric variables, but they are designed to support international character sets, not arbitrary systems in which "ab32c12xxx3" should come after ""ab3zc9xyz?bananarama".

How did you solve the problem at the application level?

13. Registered User
Join Date
May 2006
Posts
10
beilstwh the order should be:

A 1
A 3
A 10
ab32c12xxx3
el/mfdo489430j

In another case, we can have the following sort:

1 ano
A1
A 1
A 3
A 10
ab32c12xxx3
el/mfdo489430j

14. Registered User
Join Date
May 2006
Posts
10
WilliamR,

Yes "ab32c12xxx3" must be in one column. String and Numbers can be infix (MN1C) or suffix (Special Firms Team 10) or just a simple string.

I did one study over NLS_SORT and NLS_COMP they doesn't solve the problem.

In the application level I broke the original string into STRINGs and NUMBERs then I put in one Array. The sorting was processed considering this Array.

I am almost going to arrive to the conclusion that the issue "I have a alphanumeric column in the database. Is it possible to do an alphanumeric sort directly by a sql command like "order by <column>" " isn't possible in the database level.

15. Registered User
Join Date
Aug 2004
Location
France
Posts
754
Originally Posted by Aloizio
"I have a alphanumeric column in the database."
I'm sorry to say no : you have a string column, which is sorted as such - alphabetically. If you really need the sort you want to do, then I would say that the problem is that your design does not match your business rules. The only solution then is to accept it and re-design your database schema (for example, maybe you are putting things together that should be put apart : these different codings should maybe lie in different tables).

If you are ready to think about a re-design, then you can explain more precisely what you have to do and we'll be able to help you.

HTH & Regards,

rbaraer

Posting Permissions

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