Special Character NEL as a character in a column

[hi_manoj]

Hi

My job reads data form a DB2 table and writes to Oracle table. A column in my DB2 table is of character type and NEL (special char) is one of he character in the string for few rows, because of this character the string breaks in to two line and I can write this to my oracle table. Hence my job is failing

How can I remove this special character forn the string and make that string into one line.

I tried with COnvert(Char(10),' ',abc.xyz), COnvert('\n',' ',abc.xyz) but nothing is happening. I am doing this in a PX transformer

Please Suggest
Regards
Manoj

[ray.wurlod]

I assume that, by "NEL", you mean the newline character (more usually described as "LF" for linefeed). But is it? Maybe the source from which DB2 was loaded was an extract from some kind of Windows-based file, so that you may also need to be looking for carriage-return ("CR"), which can be represented as Char(13).

Do let me know if my assumption is incorrect.

[hi_manoj]

Hi,

Somehow I manage to find a solution to this issue as of now, Looks like it is working now, (not sure how it will work in future).

What I did is, I use two convert function one after another, in first convert function I replace all my valid charecter with blank, so after first convert I left with only special character
In second convert function I replace the left over(special char) to again blank. So at the end I left with only the valid character.

Hope I have explain it well.

Regards
~Manoj

[chulett]

That sounds like a variation on the dreaded "double convert" that we've documented here in the past and you could search for, should you so desire.