Re: software version number datatype

On 26 Apr 2005 15:00:38 -0700, JimStrehlow_at_data911.com wrote:

>Should Oracle consider creating a new datatype to handle data in the
>format '###.###.###.###' such as i.p. addresses and software version
>numbers?
>What sort of datatype would you call it if we could create a new data
>type?
>e.g. VERSION
>
>You can not use NUMBER format because of the multiple periods.
>Programmers currently use VARCHAR2; but you get situations where
>programmers forget and store '9.2.0.1' and '10.0.0.1' where ten-dot is
>less than nine-dot (wrong).

 If we're going for imaginary new Oracle features, how about an extra clause on ORDER BY to specify a collation function. That'd let you keep the field as VARCHAR2, but sort by whatever criteria you want. e.g.:

ORDER BY versionfield COLLATE BY some_function

 or whatever. Like the 'ORDER' method on objects, but without having to go down the objects-in-table route.

 Then it'd be easy to implement "natural sorts" which work well for version numbers.

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool