Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2009
    Posts
    31

    Unanswered: Updating Varchar data with newline character

    Hi Guys,

    I am trying to insert a line-break in a string data that I have in varchar column but it is not working. My database is on db2 9.7 on AIX. I tried CHR(10), CHR(13) and the combination of both. I also tried CHAR instead of CHR that works neither. Could you take a look at the update statement and let me know where I am going wrong
    Below is my update statement
    update db2inst1.risk_tbl set risk =
    'ACK-1|01 12|P ' || CHR(10) ||
    'PNW-7|09 08|P' || CHR(10) ||
    'PNWPR-8|09 08|P' || CHR(10) ||
    'HW 00 24|09 08|E'
    where id='2010';

    Really chr(10) should be the one but why is it not working?

    Thanks

    dbsam

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ...it is not working.
    What do you mean by "not working"?

    Got error in the update statement?
    So, copy and paste the error message(s).

    The result was not expected?
    How did you checked it?
    Write your expected result and the result you got.

  3. #3
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    Try using CHR(13) || CHR(10) instead of CHR(10) alone. In Unix chr(10) is new line and windows and other systems use CHR(13) || CHR(10) for new line.


    Satya...

  4. #4
    Join Date
    Dec 2009
    Posts
    31
    Hi Satya/Tonkuma,

    I have tried the combination of both, the update command runs successfully every-time but when the application team tries to run the cycle it fails. The application reads the next line character based upon which I can say that the rest of the data is not going to the next line.
    Also when I run the select in data studio it does not show me newline character, which it does for all other data.

    Thanks

    dbsam

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    A newline character is CHR(10). CHR(13) is carriage return. So a CHR(10) is sufficient.

    The real question was raised by tonkuma: what exactly is the problem? Maybe it is just the client that can't handle the line break correctly?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Dec 2009
    Posts
    31
    ok so the CHR(10) was working all along the issue was a hex character that was there in the data. Thanks for the help guys

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •