| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Convert LF to CR/LF in a VARCHAR
A copy of this was sent to Tyler Barnett <tbarnett_at_stdio.com>
(if that email address didn't require changing)
On Thu, 17 Jun 1999 23:11:20 -0400, you wrote:
>I need to convert all instances of linefeed (LF) in a VARCHAR2(2000)
>field to a carriage-return/linfeed combination.
>In other words, convert every instance of a hex "0A" to a hex "0D 0A".
>I have allowed enough room in the VARCHAR to make sure I don't run over
>2000 bytes :-)
>
>The SQL function called TRANSLATE, only does a one-to-one replacement.
>The SQL function called REPLACE would appear to do the job, since it can
>substitute one string for another.
>But I haven't been able to figure out from the CD-ROM documentation how
>to specify an unprintable character such as LF or CR/LF in the syntax.
>
replace( string, chr(10), chr(13) || chr(10) );
will do it.
>Here is the example straight from the CD-ROM:
>- - - - - - - - - - - - - - - - - - - - - - -
>Syntax
>REPLACE(char,search_string[,replacement_string])
>
>Purpose
>Returns char with every occurrence of search_string replaced with
>replacement_string. If replacement_string is omitted or null, all
>occurrences of search_string are removed. If search_string is null, char
>is returned. This function provides a superset of the functionality
>provided by the TRANSLATE function. TRANSLATE provides single-character,
>one-to-one substitution. REPLACE allows you to substitute one string for
>another as well as to remove character strings.
>
>Example
>SELECT REPLACE('JACK and JUE','J','BL') "Changes"
> FROM DUAL;
>
> Changes
> --------------
> BLACK and BLUE
>
>- - - - - - - - - - - - - - - - - - - - - - -
>
>Any ideas?
>
>Thanks,
>Tyler Barnett
>tbarnett_at_stdio.com
See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/
Current article is "Fine Grained Access Control", added June 8'th
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA--
 |
 |