Informix Server 10 and remove CR character in sele

问题:

I need to remove the CR character in a select in Informix Server 10.

The function chr doesn't exist in version 10, so when I try a replace like that

REPLACE(text_column, chr(10), ' ')

I get an error like that:

Routine (chr) can not be resolved. [SQL State=IX000, DB Errorcode=-674]

The function ascii(10) doesn't work either.

Thanks in advance

回答1:

I don't have access to an IDS 10 but see if this works out for you.

Bear in mind that this is an example for LF:

LF - ASCII Code 10, Line Feed;

CR - ASCII Code 13, Carriage Return.

First be sure your session has IFX_ALLOW_NEWLINE set to true:

> EXECUTE PROCEDURE IFX_ALLOW_NEWLINE('T');

Routine executed.

>

Now let's see an example:

> CREATE TABLE tab1 (col1 CHAR(100));

Table created.

> INSERT INTO tab1 VALUES ('teste' || CHR(10) || '1');

1 row(s) inserted.

> SELECT col1 FROM tab1;



col1  teste
      1

1 row(s) retrieved.

> SELECT REPLACE(col1, '
> ', ' ') FROM tab1;



(expression)  teste 1

1 row(s) retrieved.

>

Side note:

• the CHR function takes a a whole number in the range 0 through 255 and returns the corresponding single-byte ASCII code point.
• the ASCII function takes a single argument of any character data type and returns the corresponding ASCII decimal code of the first character in the argument.

回答2:

This is the code I used, originally back in 2005, before Informix acquired the CHR and ASCII functions:

CREATE PROCEDURE ascii(C CHAR) RETURNING INT AS result;
    DEFINE i INTEGER;
    IF c IS NULL THEN
        LET i = 0;
    ELSE
        SELECT val INTO i FROM ascii WHERE chr = c;
    END IF;
    RETURN i;
END PROCEDURE;

CREATE PROCEDURE chr(i INTEGER) RETURNING CHAR(1) AS result;
    DEFINE c CHAR;
    IF i < 0 OR i > 255 THEN
        RAISE EXCEPTION -746, 0, 'CHR(): integer value out of range 0..255';
    END IF;
    IF i = 0 OR i IS NULL THEN
        LET c = NULL;
    ELSE
        SELECT chr INTO c FROM ascii WHERE val = i;
    END IF;
    RETURN c;
END PROCEDURE;

CREATE TABLE ascii
(
    val     INTEGER NOT NULL UNIQUE CONSTRAINT u1_ascii,
    chr     CHAR(1) NOT NULL UNIQUE CONSTRAINT u2_ascii
);
REVOKE ALL ON ascii FROM PUBLIC;
GRANT SELECT ON ascii TO PUBLIC;

There's also a data file with numbers 1..255 and the corresponding character code. It doesn't print well on a UTF-8 terminal since half the characters (from 128..255) are broken UTF-8 when treated as UTF-8. It only works for code sets like ISO 8859-15 (8859-1, …). You could decide that ASCII really means code points 1..127; that can be made to work since the UTF-8 and ASCII representations of those code points are identical.

If you really can't generate an appropriate load file for the ASCII table, let me know (by email — see my profile; it might already be available in the IIUG Software archive) and I can send you the gzipped archive with the data as well as the SQL above (a grand total of 1716 bytes in gzipped tar file format).