Hi
My client had a problem extracting data from the Informix database and opening it in Excel.
When the customer extracted the data, special characters like chr(10) or chr(13) had already been removed, but still displayed incorrectly in the cell. (A few cells are pushed away, or double quotes cause multiple fields to merge values, etc...)
So I explained to the customer how to remove the nonprintable character like below.
Is there another way to remove the nonprintable characters?
I used nested replace function, or regex_replace function.
> insert into test values ('a'||chr(10)||'bcd'||chr(9)||'efg');
1 row(s) inserted.
> select * from test;
a a
bcd efg
$ echo "unload to test select * from test" | dbaccess -X stores_demo
Database selected.
1 row(s) unloaded.
Database closed
$ cat test
a\0abcd\09efg|
$ dbaccess stores_demo -
> SELECT REGEX_REPLACE (A, '['||CHR(1)||'-'||CHR(31)||']','') FROM TEST;
(expression) abcdefg------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------
#Informix