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.
Problem with Trim
Moderators: chulett, rschirm, roy
Re: Problem with Trim
Since your code on both sides of the = sign is identical, this will always return 'Y'. I am sure that this isn't what you intended. Did you mean <Column1> and <Column2>?hema arvind wrote:...If(UpCase(Convert(' ', '',<column_name>))=UpCase(Convert(' ', '',<column_name>))
then 'Y' else 'N' ...
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 26
- Joined: Fri Jun 30, 2006 9:24 am
Hi ArndW, I intented as column1 and column2.However both the columns have same name.I should have written the code a bit more clear.Below is the code.The left hand side convert is related to the source data conversion and the right hand side convert is related to the lookup data conversion.
If(UpCase(Convert(' ', '',<Source.column_name>))=UpCase(Convert(' ', '',<Lookup.column_name>))
then 'Y' else 'N'
If(UpCase(Convert(' ', '',<Source.column_name>))=UpCase(Convert(' ', '',<Lookup.column_name>))
then 'Y' else 'N'
Re: Problem with Trim
you can use compare function and use its return value for your proper propagation.