How to add line breaks in a query (Informix)?

I need to 开发者_Go百科do a query that updates text with a line break. I tried using \n but it inserts "\n" literally.

Example:

update table set text = "first line\nsecond line"

I want it to show that text as:

"first line
second line"

and not as "first line\nsecond line".

When I do this with .NET it works, but not on a stored procedure.

Does anyone know how to do this?

You might, perhaps, be looking for the function 'ifx_allow_newline'.

Alternatively, following the suggestion of OMG Ponies, you might be looking for the package 'ascii' from the IIUG Software Archive. Informix now has the functions ASCII() and CHR() built-in. Note that if you have older versions of Informix (anything before 11.50 — 11.70 for CHR()), these functions will not be available and you will need to consider the package from the IIUG archive.

To use a «new line» character inside a SP:

(...)
EXECUTE PROCEDURE IFX_ALLOW_NEWLINE('T');
SELECT FIRST 1
    REPLACE('Lets use a breakline here#and here#for example', '#', '
')
FROM systables;
(...)

SELECT 'Wibble' + CHAR(13) + CHAR(10) + 'Wobble'

Puts in the carriage return and the new line like \r\n in C#. You might want both if the text is ever going to be exported into a document anywhere because sometimes just newline -\n char(10) - appears as a box character for some very dreary reason that I've forgotten/never really got :-)