Re: Convert LF to CR/LF in a VARCHAR

Use the CHR function to put non-printable characters:

REPLACE(my_field, chr(12), chr(10) || chr(12))

I'm not sure if my numbers are correct but the number you use is the ASCII value for CR and LF.

Kenny Gump

Tyler Barnett wrote in message <3769B8D8.3C84008A_at_stdio.com>...
>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.
>
>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
Received on Fri Jun 18 1999 - 13:14:56 CDT