Re: What query can sort this?

aether8203_at_yahoo.com (Sean) wrote in message news:<9f0ea455.0309031737.5028d39a_at_posting.google.com>...
> I have been trying to sort on a column of data and am stumped. Does
> anyone have any advice?
>
> The column is a VARCHAR2(15)
>
> Footnote_no
> ------------
> 1
> 1.1
> 1.2
> 1.2.1
> 1.2.2
> 1.2.2.1
> 1.4
> 1.6
> 1.10

[snip]

If you want to avoid writing a stored procedure and you no the maximum count of dots in the string, you can use InStr ans SubStr and LPad. It looks rather clumsy though:

SELECT ...
ORDER BY
lpad(substr(footnote_no || '.', 1, instr(footnote_no || '.', '.', 1, 1) - 1), 6, '0') ||

lpad(nvl(substr(footnote_no || '.', instr(footnote_no || '.', '.', 1,
1) + 1, instr(footnote_no || '.', '.', 1, 2) - instr(footnote_no ||
'.', '.', 1, 1) - 1), 0), 6, '0') ||
lpad(nvl(substr(footnote_no || '.', instr(footnote_no || '.', '.', 1,
2) + 1, instr(footnote_no || '.', '.', 1, 3) - instr(footnote_no ||
'.', '.', 1, 2) - 1), 0), 6, '0') ||
lpad(nvl(substr(footnote_no || '.', instr(footnote_no || '.', '.', 1,
3) + 1, instr(footnote_no || '.', '.', 1, 4) - instr(footnote_no ||
'.', '.', 1, 4) - 1), 0), 6, '0')

The trick is to format the numbers with leading zeros. For example: 1.22.3 becomes 000001000022000003000000. Received on Tue Sep 09 2003 - 03:20:29 CDT