Home » SQL & PL/SQL » SQL & PL/SQL » Sorting strings with numbers (Oracle 10g)
Sorting strings with numbers [message #389382] Sun, 01 March 2009 13:20 Go to next message
urnikhil
Messages: 42
Registered: March 2008
Member
Hi All,

I am trying to sort a column "x" with varchar2(6) data type with following sample data.

x
---
1
10
3
2
100
3B
10A
2A
3C

I need to sort above column so that data order is as shown below.
Please help. I am a newbie. I tried using lpad and translate functions but was unsuccessful.

x
---
1
2
2A
3
3B
3C
10
10A
100

Any help would be greatly appreciated.

Nik.
Re: Sorting strings with numbers [message #389387 is a reply to message #389382] Sun, 01 March 2009 14:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This has been asked many times.

Please search before posting.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a Test case: create table and insert statements along with the result you want with these data.

Regards
Michel

Re: Sorting strings with numbers [message #389389 is a reply to message #389382] Sun, 01 March 2009 14:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
The following demonstrates how the numbers and letters can be extracted separately, then used for ordering. I have only used capital letters and assumed that there are only numbers followed by letters and no mixing or special characters. The last query is all that is needed. The intermediate step is provided only for better understanding.

-- test data:
SCOTT@orcl_11g> SELECT * FROM your_table ORDER BY x
  2  /

X
------
1
10
100
10A
2
2A
3
3B
3C

9 rows selected.


-- extract numbers and letters separately:
SCOTT@orcl_11g> SELECT x,
  2  	    TO_NUMBER (REPLACE (TRANSLATE (x, 'ABCDEFGHIJKLMNOPQRSTUVWYZ', 'A'), 'A', '')) numbers,
  3  	    REPLACE (TRANSLATE (x, '1234567890', '0'), '0', '') letters
  4  FROM   your_table
  5  ORDER  BY x
  6  /

X         NUMBERS LETTER
------ ---------- ------
1               1
10             10
100           100
10A            10 A
2               2
2A              2 A
3               3
3B              3 B
3C              3 C

9 rows selected.


-- use extracted numbers and letters for ordering:
SCOTT@orcl_11g> SELECT x
  2  FROM   your_table
  3  ORDER  BY
  4  	    TO_NUMBER (REPLACE (TRANSLATE (x, 'ABCDEFGHIJKLMNOPQRSTUVWYZ', 'A'), 'A', '')),
  5  	    REPLACE (TRANSLATE (x, '1234567890', '0'), '0', '')
  6  	    NULLS FIRST
  7  /

X
------
1
2
2A
3
3B
3C
10
10A
100

9 rows selected.

SCOTT@orcl_11g> 

Re: Sorting strings with numbers [message #389393 is a reply to message #389382] Sun, 01 March 2009 15:25 Go to previous messageGo to next message
urnikhil
Messages: 42
Registered: March 2008
Member
Dear Barbara Boehmer,

I can't expect a better reply. Thanks a lot, especially for the way you presented the solution.

Once again greatly appreciate your time and effort on this.

- Nik.

Re: Sorting strings with numbers [message #389454 is a reply to message #389389] Mon, 02 March 2009 03:03 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
From 10g there is the chance to use regular expressions as well witch may be more appropriate for this kind of purpose.

Processing ...
with your_table as (
	select trunc(dbms_random.value*100)||chr(ascii('A')+trunc(dbms_random.value*26)) as x
	from dual 
	connect by rownum <= 10
)
select *
from your_table
order by to_number(regexp_substr(x,'^[url=/wiki/:digit:]:digit:[/url]*')),
	regexp_substr(x,'[url=/wiki/:alpha:]:alpha:[/url]*$')
Query finished, retrieving results...
                    X                     
----------------------------------------- 
2L                                        
16B                                       
21I                                       
21U                                       
31F                                       
32B                                       
71X                                       
82Z                                       
90Z                                       
94E                                       

10 row(s) retrieved



Bye Alessandro

There is a bug on the rendering for the used regular expression.
first one is: ^[ [ :digit: ] ]*
second one [ [ :alpha: ] ]*$
without spaces

[Updated on: Mon, 02 March 2009 03:09]

Report message to a moderator

Re: Sorting strings with numbers [message #389524 is a reply to message #389454] Mon, 02 March 2009 07:59 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Alessandro, I believe you may need to adjust your query slightly with a NULLS FIRST:

SQL> -- Without NULLS FIRST
SQL> select * from test1
  2  order by to_number(regexp_substr(x, '^[0-9]+')),
  3      regexp_substr(x, '[A-Z]*$');

X
------
1
2A
2
3B
3C
3
10A
10
100

9 rows selected.

SQL> -- With NULLS FIRST
SQL> select * from test1
  2  order by to_number(regexp_substr(x, '^[0-9]+')),
  3      regexp_substr(x, '[A-Z]*$') nulls first;

X
------
1
2
2A
3
3B
3C
10
10A
100

9 rows selected.
Previous Topic: time differnce between two databases (merged 3)
Next Topic: Problem with LAG function
Goto Forum:
  


Current Time: Sun Dec 04 04:16:17 CST 2016

Total time taken to generate the page: 0.07477 seconds