Re: Need Help to Sort a String Like a Number

Jamuna wrote:
> Hi All:
>
> I have a column named Release which can contain alphanumeric characters
> and i need to sort this column.
>
> A sample random data set for Release values:
>
> Release
> -------------
>
> 6.1(1)
> 6.1(1a)
> 12.3(1a)C
> 12.3(1c)BW
> 7.6(4)AA
> 7.6(4)X
> 12.2(4)MB13
> 6.1(1c)
> 7.6(3a)
> 12.3(1a)BW
> 12.2(4)MB12
> 12.3(4)TPC11a
> 12.2(4)MB13a
> 12.3(4)Z
>
> After sorting, i need the following order
>
> Sorted Release
> -----------------------
>
> 6.1(1)
> 6.1(1a)
> 6.1(1c)
> 7.6(3a)
> 7.6(4)AA
> 7.6(4)X
> 12.3(1a)BW
> 12.3(1c)BW
> 12.3(1a)C
> 12.2(4)MB12
> 12.2(4)MB13
> 12.2(4)MB13a
> 12.3(4)TPC11a
> 12.3(4)Z
>
>
> Can anyone please help me with this ??
>
> Thanks,
> Jamuna
>

You might have to resort to some "trickery" with your ORDER BY clause. Try something similar to the following:

SELECT column FROM table
ORDER BY TO_NUMBER(SUBSTR(column,1,INSTR(column,'(')-1)),column;

The first part of the ORDER BY clause will order by the part of the string before the open parentheses. This value is converted to a number so that you can get numeric ordering. The second component of the ORDER BY clause tells Oracle to order all identical "numbers" by the rest of what is in that column.

Something like that might just do the trick.

HTH,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown