Search code examples
sqlinformixcarriage-returnlinefeed

Informix 11.5 SQL Select Carriage Return and Line Feed

Informix 11.5

I am trying to search for carriage returns and line feeds that may exist in a VARCHAR field. First, I need a SELECT statement to show that they exist. Second, I need to REPLACE them with a space or other character. I've tried all kinds of variations:

CHR(10) + CHR(13)

CHR(10) || CHR(13)

CHAR(13) + CHAR(10)

CHAR(13) || CHAR(10)

SELECT CHR(10) from systables;

Everything gives an error: Routine (chr) can not be resolved.

I've been searching all over and just can't find anything that works, and I'm sure this is crazy stupid easy.

Solution

  • Get the ASCII package from the IIUG

    The CHR() function was added to IDS 11.70; it isn't in IDS 11.50.

    The good news is you can add the function because IDS is an extensible server. The better news for you is that you can obtain the relevant code from the IIUG web site in the Software Archive under the Miscellaneous section as ascii.

    That should allow you to do what you need. (Note: I wrote the code way back when — before there was support built into any of the servers.)

    Windows makes things more complicated

    I was uploading the ascii.unl file and I get an error that the number of columns do not match on line 13. Have you seen this before? I'm on Windows 2008. The errors are:

    • 846: Number of values in load file is not equal to number of columns.
    • 847: Error in load file line 13.

    I hadn't seen it before, but I've not tried the file on Windows and … well, let's say life gets trickier on Windows than it is on Unix (and this bit isn't all that simple on Unix).

    First of all, the data file needs to have CRLF line endings instead of the NL-only line endings that are standard on Unix. (Note that NL, newline, is another name for LF, line feed — aka '\n'.) For most lines in the unload file, that isn't a problem.

    The two entries for which it might be (is) a problem are for CR and LF — entries 13 and 10 respectively. In theory, if the entry for line 10 contains (in C string notation) "10|\\\n\r\n" (that is, 10, pipe, backslash, newline, CRLF), all should be OK; the absence of an error message for line 10 suggests that it is OK.

    Similarly, the entry for line 13 is "13|\r\r\n", which apparently causes grief. The simplest trial fix is to add a backslash here too: "13|\\\r\r\nn". The backslash says "the next character doesn't have a special meaning". If that doesn't work, we'll probably have to try hex-escape notation: "13|\\0d\r\n" — and use dbaccess -X to enable the hex escape notation.

    With luck, one of those two (or both) will work. If neither works, come back and we'll try to think of something else.