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.

.

Relevant Pages

  • Re: Numeric valication
    ... it accepts any string EITHER ... followed by a digit AND followed by a dot which might not be ... So to further my knowledge of regexps, I will study your examples further. ...
    (microsoft.public.scripting.jscript)
  • Re: Sorted Fixed Length String
    ... >I have a String of Numerical Digits Created Using Concatenate. ... >The Strings could be from 6 Characters in Length to 11 Characters in ... >The Least Characters in a String with a Digit GREATER than 1 can ONLY be ... Dim str As String ...
    (microsoft.public.excel.programming)
  • Re: Use parentheses in translation strings
    ... I want to replace the 4 digit ... You say you're doing this "for grouping in string matching later". ... single characters for other characters, ... To unsubscribe, e-mail: beginners-unsubscribe@xxxxxxxx ...
    (perl.beginners)
  • Re: RegExp irregularity in JScript
    ... > characters, containing at least digit, one lower case and one upper case ... of characters (i.e. digits, lower case, upper case) in the string is at least 4 ...
    (microsoft.public.scripting.jscript)
  • Re: display provider name form hostname
    ... to divide the string to take only de characters from after the ... before last dot. ...
    (comp.lang.php)