Remove ASCII Code 160 in transformer

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
Vrisha
Premium Member
Premium Member
Posts: 60
Joined: Sat Jul 15, 2017 9:32 pm
Location: Texas,USA

Remove ASCII Code 160 in transformer

Post by Vrisha »

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.
Suja
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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
Vrisha
Premium Member
Premium Member
Posts: 60
Joined: Sat Jul 15, 2017 9:32 pm
Location: Texas,USA

Post by Vrisha »

Thanks Craig. I will try and get back to you.
Suja
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

I think you meant "except here in Ray Wurlod's brain" :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
Vrisha
Premium Member
Premium Member
Posts: 60
Joined: Sat Jul 15, 2017 9:32 pm
Location: Texas,USA

Post by Vrisha »

Thank you Ray and Craig. I will get back to you on this
Suja
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

chulett wrote:I think you meant "except here in Ray Wurlod's brain" :wink:
Or here.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

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...
Choose a job you love, and you will never have to work a day in your life. - Confucius
Post Reply