Remove ASCII Code 160 in transformer
Moderators: chulett, rschirm, roy
Remove ASCII Code 160 in transformer
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.
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.
Suja
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: ''
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
This is an undocumented (except here on DSXchange) feature for dealing with "extended ASCII" characters.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Or here.chulett wrote:I think you meant "except here in Ray Wurlod's brain"
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.