TO_NUM is the TO_NUMBER in a lax and european version

to_num.sql

-- Convert text to decimal number for europeans where decimal point is a comma 
--
-- This also showcase how to integrate the C code directly into your UDTF
--
-- Simply paste this gist into ACS SQL and step through the example.
--
-- Note: I am using the library QUSRSYS. I suggest you put it into your own tool library
--
-- It is a cool example how far you can go with SQL: Have fun 😀  
-- (C) Niels Liisberg 2022
--
-- This gist is distributed on an "as is" basis, without warranties 
-- or conditions of any kind, either expressed or implied.
----------------------------------------------------------------------------------------------
call qsys2.ifs_write(
    path_name => '/tmp/main.c' , 
    file_ccsid => 1208, 
    overwrite => 'REPLACE',
    line =>'
{
    char decPoint = '',''; 
    char *  p;
    char *  str = TO_NUM.NUMBER_TEXT.DAT;
    decimal(30,15)  result   = 0D;
    decimal(17,16)  temp  = 0D;
    decimal(17)     decs  = 1D;
    short decFound = 0;
    char  c = ''0'';
    char * firstDigit = NULL;
    char * lastDigit  = NULL;
    int   dec=0;
    int   prec=0;

    str[TO_NUM.NUMBER_TEXT.LEN] = ''\0''; 

    for (p = str; (c = *p) > ''\0'' ; p ++) {
        if (c >= ''0'' && c <= ''9'' ) {
            if (!firstDigit) firstDigit = p;
            lastDigit = p;
            if (decFound) {
                if (++prec <= 15) {
                    decs  *= 10D;
                    temp = (c - ''0'');
                    temp /= decs;
                    result += temp;
                }
            } else {
                if (dec < 15) {
                    result = result * 10D + (c - ''0'');
                    if (result > 0D) dec++;
                }
            }
        } else if (c == decPoint) {
            decFound = 1;
        }
    }
    if ((firstDigit > str && *(firstDigit-1) == ''-'')
    ||  (lastDigit        && *(lastDigit+1)  == ''-'')) {
        result = - result;
    }

    MAIN.RESULT = result;
} 
');

create or replace function qusrsys.to_num (
    number_text  varchar(256)
)     
returns dec( 30 , 15)
no external action
set option output=*print, commit=*none,  datfmt=*iso , dbgview = *source --list
main:begin
    
    declare result  dec( 30, 15) default 0;
    include '/tmp/main.c';
    return result;
end;

-- unit test 

values  to_num('  123.456,78 ');
values  to_num('  123.456,78- ');
values  to_num('  -123.456,78 ');

values  to_num('  123456,78 ');
values  to_num('  123456,78- ');
values  to_num(' -123456,78 ');


values  to_num('  12345678 ');
values  to_num('  12345678- ');
values  to_num(' -12345678 ');