Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Posts
    29

    Unanswered: Handle New line Characters.

    Hi,

    I have two problems

    1) In my table, I have a field which is of data type - LONG. This field contains new line characters. For e.g

    SQL Query -
    select log_field from temp_table;

    Result -

    There are many
    newline characters in
    this field.

    Question - I would like to replace the new line characters with ~. The idea is I have all the characters in one line. So the out put that I would like is

    There are many~new line characters in~this field.

    I had tried replacing - replace(log_field,chr(10),'~'), but this does not work.

    Please note this query needs to be executed in the unix environment.

    Any suggestions please?

    2) Assuming I have a data extract which is csv file. One of the fields has the new line characters. I am facing problems in loading it using sql loader.
    Any suggestions on how to tackle this problem as well.

    Many Thanks.

    Regards
    Ashish

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    LONGs have always been a problem, nothing really supports them and oracle recommends that you don't use them anymore. If the application is still being developed, I stronly suggest that you use CLOBs instead. All the string functions (substr, instr, replace...) work with CLOBs.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Feb 2005
    Posts
    29
    Changing the datatype is not possible. We need the extract from the system in the form of a csv. So I was hoping if it were possible to replace all wild characters with '~'.

    Now assuming we get the csv file with this field with new line characters, I am facing problems while loading using sql loader. Is there any way, I can overcome this problem?

    Many Thanks.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    try this:
    PHP Code:
    replace(replace(log_fieldchr(13), '~'), chr(10), '~'
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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