One Star

[resolved] Remove Special Symbol ?

After 2 days of work I found why I was getting  CANNOT INSERT NULL INTO COLUMN even though I see the text when I use tlogrow. Here's the issue -> special character. I was able to confirm by removing these symbols manually and the record got inserted into the table without any issue. Now, when I used row1.columnname.replaceAll("? ","") and run the job it does not work. When I just test it in the expression editor using ?change of mechanism? .replaceAll("? ","") it shows the correct value  change of mechanism without the symbol. Can someone please give the solution for this? Thanks in advance.
?change of mechanism? 
7 REPLIES
One Star

Re: [resolved] Remove Special Symbol ?

Hi,
Are you sure the '?' character is really what you are getting from the source? To me it looks like it is just how the system presents unrecognized character. Maybe you need to change the code page for reading the source. Is it a file? a table?     
Seventeen Stars

Re: [resolved] Remove Special Symbol ?

You have to return the error prone char as integer to get the ASCII code of it. 
I suggest you write the rows into a file and inspect it with an HEX editor.
One Star

Re: [resolved] Remove Special Symbol ?

The source is Oracle table CLOB Datatype. Yes, I'm writing the data to a file and opened it with notepad++. That's the symbol I have. 
Seventeen Stars

Re: [resolved] Remove Special Symbol ?

You are facing now the problem you do not know the char set of the original data. I suggest you ask the guys who writes into the CLOB.
You do not have the chance to write the data directly into your target database?
One Star

Re: [resolved] Remove Special Symbol ?

The source has â¿¿change of mechanismâ¿? 
Yea the initial load will be done by talend. Just one time. Since we are moving the table from oracle to sybase. Doesn't this symbol fall into special char or something? 
One Star

Re: [resolved] Remove Special Symbol ?

Finally I solved it. Thank god atleast the source was oracle which is powerful. You can convert all non ascii chars using the below function. 
REGEXP_REPLACE(youcolumn,'','') 
Kudos to Francisco Hayoz from StackOverflow for posting this function. Thanks for all the help too. 
Seventeen Stars

Re: [resolved] Remove Special Symbol ?

It would be interesting in which way these none printable chars find its way into your clob content.