This could be ugly, but you could use combinations of ltrim, rtrim, substr, and to_number to select and sort.
Another idea would be to store your IP's in 4 number fields ip_a, ip_b, ip_c, ip_d and then do your select using: select ip_a||'.'||ip_b||'.'||ip_c||'.'||ip_d from ip_table order by ip_a, ip_b, ip_c, ip_d Again, this isn't pretty, but is easier than the first idea. The problem with storing them as text is that 70 comes after 100 because text sorts by a character as it comes to it as if it were a letter. The second one will output IP's in a workable format if you are spooling output for pinging, ftp, or some other access. Rodd On Tue, 2003-03-25 at 11:54, Ray Stell wrote: > Need a trick to sort ip addresses into numerical order. I have them > stored as varchar2. If I select without a sort I get rows > in order of their character value: > > 10.0.112.1 > 10.0.113.1 > 10.0.113.2 > 10.0.12.1 > 10.0.78.1 > > I'd like to order them numerically within the octets: > > 10.0.12.1 > 10.0.78.1 > 10.0.112.1 > 10.0.113.1 > 10.0.113.2 > =============================================================== > Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC 28^D > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Rodd Holman <[EMAIL PROTECTED]> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rodd Holman INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
