OraFAQ Forum: SQL & PL/SQL » Sorting strings with numbers

Home » SQL & PL/SQL » SQL & PL/SQL » Sorting strings with numbers (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Sorting strings with numbers [message #389382]

Sun, 01 March 2009 13:20

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.

Report message to a moderator

Re: Sorting strings with numbers [message #389387 is a reply to message #389382]

Sun, 01 March 2009 14:09

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Sorting strings with numbers [message #389389 is a reply to message #389382]

Sun, 01 March 2009 14:17

Barbara Boehmer
Messages: 9106
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>

Report message to a moderator

Re: Sorting strings with numbers [message #389393 is a reply to message #389382]

Sun, 01 March 2009 15:25

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.

Report message to a moderator

Re: Sorting strings with numbers [message #389454 is a reply to message #389389]

Mon, 02 March 2009 03:03

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

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.

Report message to a moderator

Previous Topic:	time differnce between two databases (merged 3)
Next Topic:	Problem with LAG function

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Sep 10 06:43:52 CDT 2025