Convert LF to CR/LF in a VARCHAR

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 Thu Jun 17 1999 - 22:11:20 CDT