TRIM
Moderators: chulett, rschirm, roy
Hi Panic :D
First thing - do you know what character you are having. What you see as special character in your Editor is not the true identifier of what is coming in data...If you are sure of the special chatacer- Then yes, you can do a field level modification by using TRIM
OR you can use a before job routine (ExecSh) to change all the characters in the input file.
For example this one is used to remove non ascii fields:
where
tr is for translate in UNIX
'\0' is the character to replace
' ' is the character by which you want to replace
< #TARGET_FILE_DIRECTORY#/#OUTPUT_FILE_NAME# > is the input file
#TARGET_FILE_DIRECTORY#/#OUTPUT_FILE_NAME_CLN# is the cleaned file
Let me know if you are still panicking!!!!![Surprised :o](./images/smilies/icon_surprised.gif)
First thing - do you know what character you are having. What you see as special character in your Editor is not the true identifier of what is coming in data...If you are sure of the special chatacer- Then yes, you can do a field level modification by using TRIM
Code: Select all
Use TRIM( linkname.columnname, 'Character to replace','A')
For example this one is used to remove non ascii fields:
Code: Select all
tr '\0' ' ' < #TARGET_FILE_DIRECTORY#/#OUTPUT_FILE_NAME# > #TARGET_FILE_DIRECTORY#/#OUTPUT_FILE_NAME_CLN#
where
tr is for translate in UNIX
'\0' is the character to replace
' ' is the character by which you want to replace
< #TARGET_FILE_DIRECTORY#/#OUTPUT_FILE_NAME# > is the input file
#TARGET_FILE_DIRECTORY#/#OUTPUT_FILE_NAME_CLN# is the cleaned file
Let me know if you are still panicking!!!!
![Surprised :o](./images/smilies/icon_surprised.gif)
Avishek Mukherjee
Data Integration Architect
Chicago, IL, USA.
Data Integration Architect
Chicago, IL, USA.
Re: TRIM
Hi Panic...
Can you let us know what is your source and what your job is doing...The special character looks like a pipe..and if you want to replace the character by blank then I think we can use 'Replace' function..
Can you let us know what is your source and what your job is doing...The special character looks like a pipe..and if you want to replace the character by blank then I think we can use 'Replace' function..
panic wrote:Hi all
I HAVE to trim trailing special character |
or just replace with blank while trimimg
can any one please let me know the syntax
how to use exactly
Trim(%string%,[%stripchar%],[%option%])
thanks
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Either the on-line Help or the DataStage BASIC manual will give you complete descriptions of the Trim() function. You could also use the string_trim() function in a Modify stage.
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.
-
- Charter Member
- Posts: 560
- Joined: Wed Jul 13, 2005 5:36 am
- Location: Ohio
Hi:
How to eliminate a non-ascii charecter dynamically? I mean to say...I do not know what non-ascii char I would get in my source file but i need to search for non- ascii chars and clean up the file before loading into target table.
Basically my source file is from SAP. So, I can say non-ascii char would be a Korean/Japanese/chinese language specific.
I am not very good at Shell scripting. please help me out solving this.
thanks,
How to eliminate a non-ascii charecter dynamically? I mean to say...I do not know what non-ascii char I would get in my source file but i need to search for non- ascii chars and clean up the file before loading into target table.
Basically my source file is from SAP. So, I can say non-ascii char would be a Korean/Japanese/chinese language specific.
I am not very good at Shell scripting. please help me out solving this.
thanks,
avi21st wrote:Hi Panic :D
First thing - do you know what character you are having. What you see as special character in your Editor is not the true identifier of what is coming in data...If you are sure of the special chatacer- Then yes, you can do a field level modification by using TRIM
OR you can use a before job routine (ExecSh) to change all the characters in the input file.Code: Select all
Use TRIM( linkname.columnname, 'Character to replace','A')
For example this one is used to remove non ascii fields:
Code: Select all
tr '\0' ' ' < #TARGET_FILE_DIRECTORY#/#OUTPUT_FILE_NAME# > #TARGET_FILE_DIRECTORY#/#OUTPUT_FILE_NAME_CLN#
where
tr is for translate in UNIX
'\0' is the character to replace
' ' is the character by which you want to replace
< #TARGET_FILE_DIRECTORY#/#OUTPUT_FILE_NAME# > is the input file
#TARGET_FILE_DIRECTORY#/#OUTPUT_FILE_NAME_CLN# is the cleaned file
Let me know if you are still panicking!!!!
Thank you,
Anu
Anu
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
If they are Chinese-Japanese-Korean (CJK) characters are you sure you want to eliminate them? They are probably valid data. More likely you need to preserve them, would be my guess.
This means that you need DataStage National Language Support (NLS) installed, configured and the appropriate character map(s) chosen. Read more about it in the DataStage NLS manual. There is overlap of some characters in the CJK encodings.
This means that you need DataStage National Language Support (NLS) installed, configured and the appropriate character map(s) chosen. Read more about it in the DataStage NLS manual. There is overlap of some characters in the CJK encodings.
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.
I am sorry for the confusion Ray. Actually we have NLS installed and configured. I mentioned Chinese-japanese-korean as an example to explain the problem. But I do not know what type of non-ascii I would be getting.
-thanks.
-thanks.
ray.wurlod wrote:If they are Chinese-Japanese-Korean (CJK) characters are you sure you want to eliminate them? They are probably valid data. More likely you need to preserve them, would be my guess.
This means that you need DataStage National Language Support (NLS) installed, configured and the appropriate character map(s) chosen. Read more about it in the DataStage NLS manual. There is overlap of some characters in the CJK encodings.
Thank you,
Anu
Anu
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Maybe you could test the rightmost character with IsValid() to determine whether it is a valid string. A character that is valid as a ustring but not as a string might fall into your "non-ASCII" class.
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.