Home » SQL & PL/SQL » SQL & PL/SQL » "smart" Alpha-Numeric Sorting
"smart" Alpha-Numeric Sorting [message #248562] Fri, 29 June 2007 09:26 Go to next message
greenstone
Messages: 4
Registered: June 2007
Location: Alexandria, Va
Junior Member
Hi,

Would you have a suggestion of how to "smart" sort a varchar2 column that (each row) contains combined alpha-and-numeric data?

For example:

Standard sorting give me:
abc1
abc10
abc2

But I would like:
abc1
abc2
abc10

Thanks!
Re: "smart" Alpha-Numeric Sorting [message #248563 is a reply to message #248562] Fri, 29 June 2007 09:44 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

You can try with the below query if you are using 10g
select * from test order by regexp_substr(col1,'[:digit:]');


Regards
Sanka
Re: "smart" Alpha-Numeric Sorting [message #248570 is a reply to message #248562] Fri, 29 June 2007 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select * from t;
VAL
--------------------
abc1
abc10
abc100
abc21
abc3
abc2

6 rows selected.

SQL> select * from t order by to_number(regexp_substr(val,'[[:digit:]]+'));
VAL
--------------------
abc1
abc2
abc3
abc10
abc21
abc100

6 rows selected.

Regards
Michel
Re: "smart" Alpha-Numeric Sorting [message #248575 is a reply to message #248570] Fri, 29 June 2007 10:40 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

Thanks Michel.
I was a bit wrong because I've just queries the values what was given.

Regards
Sanka
Re: "smart" Alpha-Numeric Sorting [message #248578 is a reply to message #248575] Fri, 29 June 2007 10:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TO_NUMBER is the most important whatever the method you use (regexp or old instr/substr), this was the purpose of my post.

Regards
Michel
Re: "smart" Alpha-Numeric Sorting [message #248609 is a reply to message #248562] Fri, 29 June 2007 13:26 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Just curious,
will the column ALWAYS have the same non numeric characters? Will the non numeric always be in the beginning? If it doesn't then the order by would have to be changed some. how do you want it sorted if you have something like

abc1
abc100
23def
2x3c4w

...
Re: "smart" Alpha-Numeric Sorting [message #248699 is a reply to message #248609] Sat, 30 June 2007 12:43 Go to previous message
greenstone
Messages: 4
Registered: June 2007
Location: Alexandria, Va
Junior Member
Thanks for the replies!

Bill, you're right. There are some ambiguous cases like you've outlined. In cases where there is no clear alpha string, it should resort to standard alpha-numeric sorting.

The general idea is that if a user enters the same alpha string for many rows...but the string only differs by numeric values, then the list should sort in a "smart" order. (I realize that "smart" can be a bit nebulous to defined in mathmatical terms, so an example might be...)

Example:

1mine
2mine
10mine
23def
2x3c4w
abc1
abc1a
abc1b
abc1c
abc2
abc3
abc10
abc21
abc100
de3
de4
de40
def1
def2
def10
def100
Previous Topic: How to read CSV file and insert the same into a oracle table?
Next Topic: Comapring tables from two databases
Goto Forum:
  


Current Time: Sun Dec 11 02:41:41 CST 2016

Total time taken to generate the page: 0.07232 seconds