alphanumeric sort

[Wickersty]

Hi folks,

I'm a little lost on this one. I tried reading the forums for an answer but couldn't find one that worked. If I have a field called title_level, and it can contains values such as A, B, C, 1, 2, 3... I want to sort results so that they'd be listed as:

A
C
1
3
11
14
.
.
.

I've got this going on right now: ORDER BY lpad(title_level, 2) which results in this:

1
3
11
14
A
C

How can I get the letters to sort alphabetically before the numbers, and keep the numbers as they are (ie 1, 3, 11, 14 NOT 1, 11, 3, 14)?

Thanks for all your great advice!

Jeff

 

[r937]

ORDER BY 
case when left(title_level,1) between 0 and 9
     then 1 else 0 end,    
case when left(title_level,1) between 0 and 9
     then title_level
     else 0 end,    
title_level

r937.com | rudy.ca

 

[Wickersty]

left(title_level,1) didn't work (threw error message on "left", so I used substr(title_level,1,1) which I think accomplishes the same thing. However, when I use this code:

<code>
ORDER BY
CASE WHEN substr(title_level,1,1) BETWEEN 0 AND 9
THEN 1 ELSE 0 END,
CASE WHEN substr(title_level,1,1) BETWEEN 0 AND 9
THEN title_level
ELSE 0 END,
title_level
</code>

I get this error message:

[Macromedia][Oracle JDBC Driver][Oracle]ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

Any ideas?

 

[r937]

ORDER BY
CASE WHEN substr(title_level,1,1) BETWEEN 0 AND 9
     THEN 1 ELSE 0 END,    
CASE WHEN substr(title_level,1,1) BETWEEN 0 AND 9
     THEN [b]cast(title_level as number)[/b]
     ELSE 0 END,    
title_level

r937.com | rudy.ca