Month: January 2023

While on other databases available by standard, Informix does not provide such a feature. But it can be implemented easily:

CREATE FUNCTION gc_init(dummy VARCHAR(255)) RETURNING LVARCHAR;
    RETURN '';
END FUNCTION;

CREATE FUNCTION gc_iter(result LVARCHAR, value VARCHAR(255))
    RETURNING LVARCHAR;
    IF result = '' THEN
        RETURN TRIM(value);
    ELSE
        RETURN result || '  <OR>  ' || TRIM(value);
    END IF;
END FUNCTION;

CREATE FUNCTION gc_combine(partial1 LVARCHAR, partial2 LVARCHAR)
    RETURNING LVARCHAR;
    IF partial1 IS NULL OR partial1 = '' THEN
        RETURN partial2;
    ELIF partial2 IS NULL OR partial2 = '' THEN
        RETURN partial1;
    ELSE
        RETURN partial1 || '  <OR>  ' || partial2;
    END IF;
END FUNCTION;

CREATE FUNCTION gc_final(final LVARCHAR) RETURNING LVARCHAR;
    RETURN final;
END FUNCTION;

CREATE AGGREGATE group_concat
    WITH (INIT = gc_init, ITER = gc_iter,
          COMBINE = gc_combine, FINAL = gc_final);
		  
		  

…and decode back from char to int.

This is an implemenation I once made to run on an informix database. It is probably used not very often, but I somehow liked the code, therefore I want to share it to everyone:

CREATE FUNCTION EncodeIntToChar( a INT, len INT )
	RETURNING VARCHAR(100);

	-- Function encodes a numeric value (int) to an alphanumeric representation
	-- with a specific length (len)

	DEFINE Result VARCHAR(100);
	DEFINE i INT;

	LET i = 0;
	LET Result = '';

	FOR i = (len - 1) TO 0
		LET Result = TRIM(Result)||CHR( MOD( a / POW( 36, i ), 36 ) + ( 48 + ( CASE WHEN MOD( a / POW( 36, i ), 36 ) > 9 THEN 7 ELSE 0 END ) ) );
	END FOR;

	RETURN Result;
END FUNCTION;

CREATE FUNCTION CharDecodeToInt( a VARCHAR(100), len INT )
	RETURNING INT;

	-- Function decodes an alphanumeric value to its integral representation
	-- Specification of length is also required
	
	DEFINE Factor, Result, Power, i INT;

	LET a = LPAD( RIGHT( a, len ), len, '0' );
	LET Result = 0;

	FOR i = len TO 1
		LET Power = ABS( i - len );
		LET Factor = ( ASCII( SUBSTR( a, i, 1 ) ) - 48 - ( CASE WHEN ( ASCII( SUBSTR( a, i, 1 ) )) > 57 THEN 7 ELSE 0 END ) );
		LET Result = Result + ( Factor * POW( 36, Power ) );
	END FOR;

	RETURN Result;
END FUNCTION;

CREATE FUNCTION IncreaseEncoded( AEncoded VARCHAR(100) )
	RETURNING VARCHAR(100);
	RETURN EncodeIntToChar( CharDecodeToInt( AEncoded, LENGTH( AEncoded ) ) + 1, LENGTH( AEncoded ) );
END FUNCTION;