Problem with Trim

[hema arvind]

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.

[ArndW]

...If(UpCase(Convert(' ', '',<column_name>))=UpCase(Convert(' ', '',<column_name>))
then 'Y' else 'N' ...

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]

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'

[sachin1]

you can use compare function and use its return value for your proper propagation.

[rafik2k]

Can you check using simple trim and upcase funtion only

If(UpCase(trim(<Source.column_name>))=UpCase(trim(<Lookup.column_name>))
then 'Y' else 'N'

BTW what is metadata for above mentioned table?