DSXchange

I have requirement to trim the 'Phone_number' columns.

Input(source) output(target)
PHONE_NUM-Char(15) PHONE_NUM-Varchar(15)

When I used the function Trim(PHONE_NUM), it trimmed the leading and trailing spaces, but the job failed with an error below
"1. Error code: 12899, Error message: ORA-12899: value too large for column "MSPO_TEST"."D_VENDORS1"."PHONE_NUM" (actual: 16, maximum: 15)."


Trim will remove only the spaces from front and back of the string.

When I investigated unable to see the 'ASCII Code 160' in the source oracle database and it is invisible. When I copied into the notepad with hex editor, found the 'ASCII Code 160'.

Another try I did is when I increased the output column datalength to Varchar(16) , the job ran fine. Came to know that I am not allowed to increase the datalength in target table

Please let me know how to remove this 'ASCII Code 160' in transformer. Thanks.

Use the CHAR() function to represent it, if that's the decimal value then CHAR(160) should do the trick. Then use either Convert() or EReplace() to remove it by replacing it with nothing, typically quote quote, i.e. an empty string: ''

Thanks Craig. I will try and get back to you.

If Char(160) does not work in your expression, try Char(160,1).

This is an undocumented (except here on DSXchange) feature for dealing with "extended ASCII" characters.

I think you meant "except here in Ray Wurlod's brain"

Thank you Ray and Craig. I will get back to you on this

chulett wrote:I think you meant "except here in Ray Wurlod's brain"

Or here.

In other words, yet another Ray Wurlod post, one with a bit more explanation of something that "isn't in the manual".

I think it has been documented since at least version 8.5. They call it the "allow8bits" option. Maybe it's not all that clear what it means...