Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Function to remove spaces/carriage returns from text
On Wed, 29 Nov 2006 22:46:50 +0000 (UTC), "Robert C. Williamson"
<robw_at_umd.edu> 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
Use the TRANSLATE function:
TRANSLATE(text, 'x'||CHR(10)||CHR(13),'x')
this converts
'x' to 'x',
CHR(10) (linefeed) to nothing, and
CHR(13) (carriage return) to nothing
and leaves every other byte value in text as it is.
The dummy ('x') is necessary because the third argument is not allowed to be empty.
Regards,
Jaap. Received on Wed Nov 29 2006 - 17:04:33 CST