SET TERM ^ ;

CREATE OR ALTER function FN_JSON_V5_NUMBER (
    JSON blob sub_type 1 segment size 80,
    CAMPO varchar(50))
returns blob sub_type 1 segment size 80
AS
declare variable retorno blob sub_type 1 segment size 80;
declare variable pos integer;
begin
    RETORNO = '';

    POS = POSITION ('"'||:CAMPO||'":', :JSON, 1);
    if(POS > 0)then
    begin
        RETORNO = SUBSTRING (:JSON FROM (:POS + CHAR_LENGTH ('"'||:CAMPO||'":')) FOR CHAR_LENGTH (:JSON) - 0 + 1);

        POS = POSITION (',', :RETORNO, 2);

        IF (:POS > 1) THEN
        BEGIN
            RETORNO = SUBSTRING (:RETORNO FROM 1 FOR (:POS - 1));
        END ELSE
        BEGIN
            POS = POSITION ('"', :RETORNO, 2);
    
            IF (:POS > 1) THEN
            BEGIN
                RETORNO = SUBSTRING (:RETORNO FROM 1 FOR (:POS - 1));
            END ELSE
            BEGIN
                POS = POSITION ('}', :RETORNO, 2);
    
                IF (:POS > 1) THEN
                BEGIN
                    RETORNO = SUBSTRING (:RETORNO FROM 1 FOR (:POS - 1));
                END
            END
        END

    end

    RETORNO = replace(RETORNO,'"','');
    RETORNO = replace(RETORNO,'"','');

    IF(TRIM(RETORNO) = '') THEN
    BEGIN
        RETORNO = '0';
    END

    return RETORNO;
end
^

SET TERM ; ^


