Informix 9.52C1 - replace carriage return and line feed
<p>I search google and couldn't find anything that really helped me IBM website show Informix 11.5 and up functions, like if they have stop supporting 9.52C1 or something. Hence the reason I am here.</p>

<p>As the topic stated I'm using Informix 9.52C1. I got this information using the SQL:</p>

<pre><code>SELECT owner FROM systables WHERE TABNAME= ' VERSION';
</code></pre>

<p>I'm not sure if the replace function is even supported because when I execute the statement:</p>

<pre><code>SELECT col1, REPLACE('r','p','poster') as col2 FROM table;
</code></pre>

<p>The column col2 contains just the letter 'r' however no error was thrown although I used the REPLACE() function.</p>

<p>I know the escape characters (CRLF) can be found using the "\r\n" escape format because this SQL worked accordingly:</p>

<pre><code>SELECT *  FROM table WHERE col1 LIKE '%\r\n%';
</code></pre>

<p><em>Note: I used the above SQL to retrieve the version number as I only had access via a client DB application. If however you have access to the actual host running the informix DB server I'm sure there is some command arguement to resolve the version maybe the defualt linux --version or -v arguement with the db server application.</em></p>

<p>And this indeed returned only the records that contained CRLF.</p>

<p>My main job is to place a backslash in front of CR and LF as I am migrating data from Informix 9.52C1 to a PostgreSQL database. I'm planning to use the COPY function to load the data to the PostgreSQL database and the COPY function works in this manner as I have done a test record. My dilemma is extracting the data from Informix in the correct format. Can anyone assist with this issue?</p>

<p>I have tried:</p>

<pre><code>SELECT REPLACE('\\\r\\\n','\r\n',col1) as description FROM table;
</code></pre>

<p>However this didn't work I believe due to the replace function, as I have mention I am not sure if the replace function is available in this Informix version.</p>

<p>Thanks in advance,</p>

<p>jerg</p>

<p><em>P.S. None of the functions ASCII(),CHAR() and CHR() worked either. Some sites suggested these function. But as far as I can see the functions CHR() &amp; ASCII() was implemented in verison 11.5+. This <a href="https://stackoverflow.com/questions/1337196/sql-query-for-a-carriage-return-in-a-string-and-ultimately-removing-carriage-ret">post</a> suggested the functions.</em></p>