carriage returns

[helpme]

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

[reydp]

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.

[julian]

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.

[UNIX DBA]

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.

[UNIX DBA]

I swear I was first

[reydp]

I've missed that function again. I could have use combination of functions instead.

Thanks Julian.

[helpme]

Thanx guysss
replacing chr(10) did work..13 dint..:(
Thanx a lot!!!!
Sam

[julian]

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.

[jm]

try this

from unix

col < badfilename > newfilename

[UNIX DBA]

Just mean I got the ascii value wrong, I did it from memory