DSXchange

I am reading a file that has a multi-value field. The data in the multi-value field is separated with a special character( looks like a box). When we used oracle 9 it would convert the box to a question mark. Then in the transformer, I would convert the ? to blanks. Now that we have converted to oracle 10g. It does not convert it to a question mark instead it leaves it as a box. How can I convert this special character (box) to blank within datastage.

Thanks!!

You need to determine exactly what this character is. Get yourself a hex editor (many free ones out there) and use that to find the hex value, then we'll see just how special it really is.

First of all, I would make sure the NLS mapping is right for the source connection - is it UTF8? ISO8859 (extended ASCII)?

Then, I would write a Transformer that breaks up the input value into individual characters and writes out the UNIPOS() value of each one. You can then use this value to do something like CONVERT( UNICHAR(xxx), "?", input.value ), where xxx is the UNIPOS value, and ? is the character that you want to convert it to. Or you could use the UNICHAR(xxx) in a FIELD function call to split on it.

Update: Here is the code for a Routine to convert a string to unicode values

May I suggest a couple of minor improvements? One is to use the special assignment operator (:=) which is more and more efficient the longer the string gets. The other is to use a fixed width format for each Unicode codepoint.
(An option not implemented is to convert each to hex. That could be achieved by applying an "MX" conversion.)