Re: numeric sorting on a char field having a decimal

bozon wrote: > Art S. Kagel said > select ltrim(numberfield)... order by 1; > > I don't think that is quite right because if I do that I get this: > > 10.5 > 10.50 > 7.11 > 7.23 > 7.24 > 7.29 > 7.34 > 7.4 > 7.40 > 7.45 > 7.5 > 8.11 > 8.23 > 8.24 > 8.29 > 8.34 > 8.4 > 8.40 > 8.45 > 8.5 > > Of course if the data has no number over 9.9999 then your way does work. We also need to know what to do if there's a second dot in the string, and what to do if there are non-numeric characters around (like 10.00.UC3). With a complete specification of the problem, it is probably soluble. Let's make some convenient assumptions: there are only digits and a single dot (period) in the strings, and the dot separates non-empty digit strings. Then, the requirement is to sort the data such that the values before the dot are in ascending numerical sequence and the characters after the dot are in ascending numerical sequence within groups of the first number. This means that 010.000 and 10.00 will sort equal, of course - does that matter? We need: SELECT version_string FROM ContainingTable ORDER BY <numeric-value-before-dot>, <numeric-value-after-dot> (This exploits the possibility of sorting by values that are not in the final select list - added in IDS 9.40, IIRC). If we postulate a function FIND_CHAR(str, ch) that will return the position of the first occurrence of character/string 'ch' in str (indexing from 1, returning the length of the string plus 1 when there is no occurrence), then the ORDER BY clause can be written: ORDER BY SUBSTR(version_string, 1, FIND_CHAR(version_string, '.')-1)+0, SUBSTR(version_string, FIND_CHAR(version_string, '.')+1)+0 The unfortunate part of this is that you have to postulate the FIND_CHAR() function; it isn't built into IDS as specified (and I didn't find an equivalent in the documentation). There have been SPL equivalent functions produced in the past - not fast but at least they work - and I'm fairly sure there have been some bladelets produced with that functionality. If FIND_CHAR() returns a different value such as zero when the search fails, or SUBSTR barfs on starting positions beyond the end of the string, you might be better off with: SELECT version_string AS v, FIND_CHAR(version_string, '.') AS d FROM SomeTable WHERE FIND_CHAR(version_string, '.') != <failure value> INTO TEMP vstrings; SELECT v AS version_string FROM vstrings ORDER BY SUBSTR(v, 1, d-1)+0, SUBSTR(v, d+1)+0; You could condition the first select so that LENGTH >= 3 and FIND_CHAR() BETWEEN 2 AND LENGTH(version_string)-1 and version_string MATCHES "[0-9]*.[0-9]*" and so on and so forth. Note that the MATCHES clause doesn't do a full regex - it just ensures there's at least one digit before a dot and at least one digit afterwards, but there could be other garbage in there too.