Trim() Function
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 26
- Joined: Fri Jun 30, 2006 9:24 am
Trim() Function
Hi,
I have a requirement which needs the following output.
If have a string as " XXX YYY ZZZ" ,the output should be
XXXYYYZZZ.The output should not have any spaces,tabs,nulls.IT should return only all the characters what it contains.
I tried the option of trim("XXX YYY ZZZ"," ","A").However I am still getting some strings which are containing spaces.
Please suggest on this.
I have a requirement which needs the following output.
If have a string as " XXX YYY ZZZ" ,the output should be
XXXYYYZZZ.The output should not have any spaces,tabs,nulls.IT should return only all the characters what it contains.
I tried the option of trim("XXX YYY ZZZ"," ","A").However I am still getting some strings which are containing spaces.
Please suggest on this.
It this a server or a px job? A recent similar post showed that the string actually contained unprintable characters - could this be the case for you?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 26
- Joined: Fri Jun 30, 2006 9:24 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Read the documentation on Trim(). It does not remove internal spaces; it reduces multiple contiguous occurrences to a single occurrence. Perhaps you need Convert() instead?
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: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
Yeah as Ray says you can use Convert to get your job done.
Use this instead of trim()
Convert(' ', '','XXX YYY ZZZ')
would give you the ouptut
XXXYYYZZZ
But notice that the first field has a space between the single quotes whereas the second quote has nothing between the quotes.
Use this instead of trim()
Convert(' ', '','XXX YYY ZZZ')
would give you the ouptut
XXXYYYZZZ
But notice that the first field has a space between the single quotes whereas the second quote has nothing between the quotes.
Minhajuddin
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
That's why we have Code tags!
Code: Select all
Convert(" ", "", InLink.TheString)
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: 26
- Joined: Fri Jun 30, 2006 9:24 am
Thank You Very Much for your help.
I tried to use convert as you told and it worked fine.
However,now I have a requirement such that If I have two strings(one from source and one from lookup). I have to compare them for equality and case insensitivity.If both the strings are not equal in every aspect,then the string from the source should be loaded to target.
The code which I used before was
If upcase(trim(<column_name>,' ',"A")) =
upcase(trim(<column_name>,' ',"A")) then 'Y' else 'N'
I declared this code in stage variable and In target's constraint,I added the condition stagevar='N'
After your suggestion of using convert,I tried using the below code
If(UpCase(Convert(' ', '',<column_name>))=UpCase(Convert(' ', '',<column_name>))
then 'Y' else 'N'
However they are returning some strings which are present in both source and look up.
Please suggest what else checks could be performed to check for sting equality so that same strings could not be loaded to target.
For example,If I have two strings "Chinese Hamster" and "Chinese hamster" only one string should be returned to the target.ie. no duplications are allowed.
I tried to use convert as you told and it worked fine.
However,now I have a requirement such that If I have two strings(one from source and one from lookup). I have to compare them for equality and case insensitivity.If both the strings are not equal in every aspect,then the string from the source should be loaded to target.
The code which I used before was
If upcase(trim(<column_name>,' ',"A")) =
upcase(trim(<column_name>,' ',"A")) then 'Y' else 'N'
I declared this code in stage variable and In target's constraint,I added the condition stagevar='N'
After your suggestion of using convert,I tried using the below code
If(UpCase(Convert(' ', '',<column_name>))=UpCase(Convert(' ', '',<column_name>))
then 'Y' else 'N'
However they are returning some strings which are present in both source and look up.
Please suggest what else checks could be performed to check for sting equality so that same strings could not be loaded to target.
For example,If I have two strings "Chinese Hamster" and "Chinese hamster" only one string should be returned to the target.ie. no duplications are allowed.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
This really begs a separate thread. On the server forum. Investigate the Compare() function. Decide your business rule for casing in the output and generate that.
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.