Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184

    Unanswered: Identify and replace \n (hard return) in record column

    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.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You are going to need to write a UDF (User Defined Function).

    Andy

  3. #3
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184
    Quote Originally Posted by ARWinner
    You are going to need to write a UDF (User Defined Function).

    Andy
    Thanks for the input.

    Can you please give additional information.

    TIA.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184
    Thanks a million, Andy!

    That solved the issue.

    However, can we also use the REPLACE function?

    Thanks again for the timely help. God bless.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Yes, you can use the REPLACE function.

    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •