I need to identify & replace all occurrences of hard return characters in a VARCHAR defined attribute/column.

How do I do this?

For example, the content of the record is as follows: (multiple lines)
No provider for smtp
javax.mail.NoSuchProviderException
No provider for smtp ( Cause - No provider

The above I need to change to ...(1 line)
No provider for smtp javax.mail.NoSuchProviderException No provider for smtp ( Cause - No provider

Thanks in advance.

You are going to need to write a UDF (User Defined Function).

Andy

Thanks for the input.

Can you please give additional information.

TIA.

You need to create something like this:

CREATE function andy.UDF_STRIP_CR( strIn VARCHAR(8000) )
RETURNS VARCHAR(8000)
LANGUAGE SQL DETERMINISTIC
NO EXTERNAL ACTION CALLED ON NULL INPUT
------------------------------------------------------------------------
-- SQL UDF (Scalar)
------------------------------------------------------------------------
F1: BEGIN ATOMIC
DECLARE strOut VARCHAR(8000) ;
DECLARE nLen, nCnt INT ;

SET strOut = '' ;
SET nLen = LENGTH(strIn) ;
SET nCnt = 1 ;

IF ( strIn IS NULL ) THEN
RETURN NULL ;
END IF ;

WHILE ( nCnt <= nLen ) DO
IF (( SUBSTR(strIn,nCnt,1) <> CHR(13)) and ( SUBSTR(strIn,nCnt,1) <> CHR(10)))
THEN SET strOut = strOut || SUBSTR(strIn,nCnt,1);
END IF ;
SET nCnt = nCnt + 1 ;
END WHILE ;

RETURN strOut ;
END


Then you use it to translate the data in a query.

select andy.udf_strip_cr(text) from mytable.


Andy

Thanks a million, Andy!

That solved the issue.

However, can we also use the REPLACE function?

Thanks again for the timely help. God bless.

Yes, you can use the REPLACE function.

Andy