Home » SQL & PL/SQL » SQL & PL/SQL » order by letters then numbers (oracle 9)
order by letters then numbers [message #337928] Fri, 01 August 2008 10:33 Go to next message
cygnusx04
Messages: 16
Registered: February 2008
Junior Member
I have a view on a table containing the following type of information (field = name).

110/3D-2
113/27A-2
15/26B-5
22/S7A-3Z
9/21-2
9/9A-8
ANDREW
ALISON
JOHN
TERRY
WAYNE

the above is when using the order by clause on name.

I want to order so I see letters first and then numbers.

i.e ANDREW......WAYNE........110/3D-2......9/9A-8


I can either modify the view or use ORDER by clause. I've looked at using DECODE and SUBSTR, but have got no where with it.

any ideas?
Re: order by letters then numbers [message #337931 is a reply to message #337928] Fri, 01 August 2008 10:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
One idea:
order by case when substr(col,1,1) between '0' and '9' then '[' else ' ' end, col

Regards
Michel

[Updated on: Fri, 01 August 2008 10:57]

Report message to a moderator

Re: order by letters then numbers [message #337949 is a reply to message #337928] Fri, 01 August 2008 12:40 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
What makes 110/3D-2 come before 9/9A-8 ?
Re: order by letters then numbers [message #337970 is a reply to message #337949] Fri, 01 August 2008 15:22 Go to previous message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
And for that matter, ANDREW before ALLISON?
Previous Topic: pl sql insert
Next Topic: if else syntax
Goto Forum:
  


Current Time: Wed Dec 07 12:54:34 CST 2016

Total time taken to generate the page: 0.11895 seconds