Problem with Trim
Posted: Tue Jun 19, 2007 2:55 am
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.
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.