SET TERM ^ ;

create or alter function FN_JSON_ARRAY2 (
    JSON varchar(5000),
    CAMPO varchar(100))
returns varchar(500)
AS
DECLARE VARIABLE RETORNO VARCHAR(500);
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));
        POS = POSITION (',"', :RETORNO, 2);

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

    END

    POS = POSITION('"', :RETORNO, 1);

    IF (POS = 1) THEN
    BEGIN
        RETORNO = SUBSTRING (:RETORNO FROM 2 FOR CHAR_LENGTH(:RETORNO));
    END

    RETURN RETORNO;
END
^

SET TERM ; ^

