Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Function to remove spaces/carriage returns from text

Re: Function to remove spaces/carriage returns from text

From: Jaap W. van Dijk <j.w.vandijk.removethis_at_hetnet.nl>
Date: Wed, 29 Nov 2006 23:04:33 GMT
Message-ID: <456e10e5.431250@news.hetnet.nl>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US