How to ignore or replace "carriage return" in informix 4GL

Question

Say I have the following query:

insert into myTable values ("TEST 1 
TEST 2")

Then I'm selecting the description to output to an excel sheet:

select description from myTable

Result:

description TEST 1
TEST 2

This will result the output for the single-line description column to be split on 2 separate lines in the .xls output.

How can I resolve this so I get the entire string on 1 line.

Can we loop it through and find the carriage return and replace it? Or?

Thank you.

Silly question time: if you don't want newlines or carriage returns in the data, why do you insert such characters? — Jonathan Leffler, Mar 28 '17 at 17:39
Right, but I am not the owner of the program where the insert is being done. I'm writing a report pulling information from all over and their inserts are being done from all over as well. For a quicker solution I wanted to first make sure that the report works. Then I can track down the developers of the original programs and research where the inserts are made. — Moses Davidowitz, Mar 28 '17 at 17:43
There's usually a reason — it is worth asking the question, but you have an answer. 'Hysterical raisins', better known perhaps as 'historical reasons', are depressingly commonly the problem. — Jonathan Leffler, Mar 28 '17 at 17:46

score 6 · Accepted Answer · edited Mar 28 '17 at 17:38

6

Using the "replace" clause, you can do the following:

select replace(replace(description,chr(10),' '),chr(13),' ') from myTable

edited Mar 28 '17 at 17:38

Jonathan Leffler

answered Mar 28 '17 at 17:09

mihirp724

1 Answers1