Re: Function to remove spaces/carriage returns from text

Robert C. Williamson wrote:
> Does anybody know how to remove a carriage return
> and line feed from text in an sql statement using some function
> or combination of functions.
>
> We have a text box in a PHP front end that allows the carriage return to
> be place into a description ( Text ) field.
> We then later access the database to create a unix file with that
> description in the file.
> A perl script then reads the file and puts this text into a static web
> page every night.
> If the data is entered with a carriage return the script fails.
>
> We need to remove the carriage return or line feed or both.
> Is there a proper function in SQL and how do we actually search for
> a carriage return/ line feed? How would we represent it in sqlplus
> for the search?
>
> Thanks
> Rob

It's a character field of some kind?

update mytable set mycolumn =
replace(mycolumn,chr(ascii_value),chr(32));

Check the ascii tables for sure but isn't 13 a carriage return and 10 a new line?

( 32 should be a space or 20 in hex ).

You could put in a plsql procedure to first select out only the rows that you need to change ( use instr perhaps to find them ).

If you use a high enough level of oracle you can use regular expressions to change. Received on Wed Nov 29 2006 - 17:01:17 CST