Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    3

    Unanswered: Newline characters in Oracle

    Hi,

    I have a problem with the newlines characters stored in the database fields.

    I need to print out all the database values into a text file separated by pipelines between columns. (e.g. col1|col2|col3|col4|col5). However, for column 4 which is a columm that stores user remarks always contains newline characters. Thus when I print the values out into the text file there will always a break in rows.
    (e.g. col1|col2|col3|col
    4|col5)

    This will pose a problem here when I wanna to load the text file of values into another application database using SQLloader.

    Is there anyway to replace newline characters (as well as tab characters) with a space character ?

    (P.S: My database is Oracle 9i. I use sqlplus spool function to print values into the spool file.)

    THANKS in advance to whoever tat can provide me help.

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    Replace(String, Search_String, Replacement_String)

    So in your example

    Select Replace(col4,chr(10),'')
    From Table
    Where .....

    will replace the ascii character 10 (carriage return) with nothing. If you would like to replace with a singe space use ,' ' instead of ''

    HIH

  3. #3
    Join Date
    Jun 2004
    Posts
    3
    Hi Carlos,

    THANKS !!!

    After I apply the Replace method, most of the string did not break in the text file. However, there are still some lines still stubbornly breaks.

    Is there any other ASCII characters to detect ?

  4. #4
    Join Date
    Apr 2004
    Posts
    35
    check for chr(13) which is ascii code for carriage return.

    skg

  5. #5
    Join Date
    Jan 2012
    Posts
    1

    Yay!

    dbforums has saved my life again! Thanks for asking the right question and providing the right answer!

Posting Permissions

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