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
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
:First be sure your session has IFX_ALLOW_NEWLINE set to true:
Now let's see an example:
Side note:
This is the code I used, originally back in 2005, before Informix acquired the CHR and ASCII functions:
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).