DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: carriage returns

  1. #1
    Join Date
    Jun 2001
    Posts
    316
    Hi,
    I ve stored in a column an entire file from the server.
    Now the no of files are bout 1000..now the problem is each of thses files have been modified in an editor under windows.
    So all thse files do have a carriage return at the end of each line.
    Is these a simple possible way to replace this carriage return char with blank space when i rettive that column?
    Thanx
    Sam

  2. #2
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Hi,
    I will give a sample SQL statement that will find the carriage return char by using the equivalent chr(10).
    SQL> select instr('dsflkjdfldsdsf
    2 dfdsfdsfd',chr(10)) from dual;

    INSTR('DSFLKJDFLDSDSFDFDSFDSFD',CHR(10))
    ----------------------------------------
    15

    from here you can identify the position of the carriage return character
    which you can then use to substitute with the blank character using other function such as SUBSTR, etc.

    good luck.

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Hi Sam,

    Assume that the column c1 of table TEST has carriage return(s) which you want to eliminate:

    select REPLACE(c1,chr(10),' ') from TEST;

    replaces all the carriage returns i.e, chr(10) values with a space.


  4. #4
    Join Date
    Sep 2001
    Posts
    112
    I'm sure there probably is using replace() and the chr() functions.

    something like

    select replace(column,chr(13),'') from table

    where 13 is the correct ascii number for a carriage return, it could be 11.

  5. #5
    Join Date
    Sep 2001
    Posts
    112
    I swear I was first

  6. #6
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    I've missed that function again. I could have use combination of functions instead.

    Thanks Julian.

  7. #7
    Join Date
    Jun 2001
    Posts
    316
    Thanx guysss
    replacing chr(10) did work..13 dint..:(
    Thanx a lot!!!!
    Sam

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Something to add to this topic:

    10 is ascii value of carriage return in most character sets. But could be that in some character sets it is not.




  9. #9
    Join Date
    Nov 2000
    Posts
    245

    try this

    from unix

    col < badfilename > newfilename


  10. #10
    Join Date
    Sep 2001
    Posts
    112
    Just mean I got the ascii value wrong, I did it from memory

Posting Permissions

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


Click Here to Expand Forum to Full Width