SQL any sort

any_sort

-- Any_sort solves the problem where you have an alphanumeric column 
-- that contains mixed numeric and alphanumeric data and you need to 
-- order both alphanumeric AND numeric at the same time 
-- 
-- Any sort returns a varchar that left adjust alpha and right adjust numeric values
--
-- Simply copy this gist and paste it into ACS SQL prompt. Select "run all" to build this feature.
--
-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library
--
-- You need to have the "ifs_write" procedure found on my gist.
-- I doubt this method is a good idea to build huge applications, 
-- however it is a cool example how far you can go with SQL:
----------------------------------------------------------------------------------------------
call qusrsys.ifs_write('/tmp/include.c' , '
{
    #include <stdlib.h>
    #define MAXDECS 5
    #define MAXLEN  32
    #define TRUE 1
    #define FALSE 0
    
    
    char * p;
    char * start = ANY_SORT.KEY.DAT;
    char * res = MAIN.RES.DAT;
    char decPosFound = FALSE, decFound = FALSE;
    int afterLen =0, beforeLen =0, bufLen=0;
    char afterBuf[MAXDECS]; 
    char beforeBuf[MAXLEN]; 
    char buf[MAXLEN]; 

    ANY_SORT.KEY.DAT[ANY_SORT.KEY.LEN] =0;
    
    // Count digits befor the decimal point
    for (p=start; *p ; p++) {
        if (*p >= ''0'' && *p <= ''9'') {
            decFound = TRUE;
            if (decPosFound) {
                if (afterLen < MAXDECS ) {
                    afterBuf  [afterLen ++] = *p;
                }
            } else {
                if (beforeLen < MAXLEN - MAXDECS  ) {
                    beforeBuf [beforeLen ++] = *p;
                }
            }  
        } else if (*p == ANY_SORT.DECCHAR[0]) {
            decPosFound = TRUE;
        } else if (*p <= '' '') {
            // NOP - SKIP
        } else {
            if (decFound) break; // Already have decimals
            if (bufLen < MAXLEN) {
                buf[bufLen++] = *p;
            }
        }
    }
    // Allign data into result 
    if (decFound) {
        MAIN.RES.LEN = MAXLEN; // Use all 32
        memset(res , '' '' , MAXLEN);
        memcpy(res , buf  , bufLen);
        memcpy(res + MAXLEN - MAXDECS             , afterBuf  , afterLen);
        memcpy(res + MAXLEN - MAXDECS - beforeLen , beforeBuf , beforeLen);
    } else {
        MAIN.RES.LEN = bufLen; // Use all 32
        memcpy(res , buf  , bufLen);
    }  
}       
');


create or replace function qusrsys.any_sort (
    key     varchar(256),
    decchar char(1) default '.'
) 
returns  varchar(32)
external action 
modifies sql data 
deterministic
set option output=*print, commit=*none, dbgview = *source --list
main:begin
  
    declare res varchar(64) default '';
    include '/tmp/include.c';
    return res;
  
end;


-- Use cases:

-- A real usecase; this is the intention usage of the UDTF. Look at the "order by":
Select * 
from qiws.QCUSTCDT
order by any_sort(street);

-- And here it how it works:

-- return numeric right ajust
values (
    qusrsys.any_sort ('123')
); 
-- return numeric right ajust upto max 5 digits after decimal point
values (
    qusrsys.any_sort (123.56)
); 
-- Combi: Alpha gors left and numeric goes right
values (
    qusrsys.any_sort ('abc 123.56')
); 
-- Skip trailoing chars after decimals
values (
    qusrsys.any_sort ('abc 123.56 x')
); 

-- Don't abbend if overflow 
values (
    qusrsys.any_sort ('abcabcabcabcabcabcabcabcabcabcabcabcabc 123456789123456789123456789123456789.123456789 xyzxyzxyzxyzxyzxyzxyz')
);
values (
    qusrsys.any_sort ('abcabcabcabcabcabcabcabcabcabcabcabcabc 1234 xyzxyzxyzxyzxyzxyzxyz')
);

-- Decimal point can be given  
values (
    qusrsys.any_sort ('abc 123,56 x', ',')
); 
-- Sign is just a dash and wil be consideret ad alpha
values (
    qusrsys.any_sort ('abc -123.56')
);