Trimming the field

[sujaoschin]

There are 2 input columns A & B where the values will be like below

Column A has values like
smd 14 pin
SMD 16 PIN
SMD
SOT-23

Column B has values like
1
1
1
1

Output column C =Populate the values 'if the column B =1, then concatenate"SMD": column A values'

The result is
SMD smd 14 pin
SMD SMD 16 PIN
SMD SMD
SMD SOT-23

If SMD is repeating twice in the output value , I need to remove the 2nd SMD and populate the output like 'SMD 14 pin'. Please advise.

[chulett]

What part do you need help with? The check and concatenation is simple, yes? And it seems like you need to wrap it in a little 'if-then-else' magic to check if A is already 'SMD'.

What have you tried? What's not working about it?

[sujaoschin]

I did the concatenation and got the result as

'SMD smd 14 pin'

From this above result, I need to remove the 2nd 'smd' as it is repeating twice and final output should be 'SMD 14 pin'.

[sujaoschin]

What I did now is before concatenation with SMD , I checked for the field B is having 'SMD' or 'smd' using the expression below like

if column B = 1 AND LEFT(column A value,1,3) <>'smd' then "SMD ":Column A value
else
Column A value


The above command is checking for 'smd' alone. Is there any command in datastage server to get he first 3 characters irrespective of the UPPER OR LOWER case using LEFT.

[ray.wurlod]

Just wrap an UpCase() or DownCase() function around whatever it is you're comparing.

[sujaoschin]

Thank you and I used the Downcase () and Upcase () and resolved this problem.

[ray.wurlod]

One should have done it.

[fareeda_b]

HI SujathaK,

Could you please give me xplanation how you used the Downcase () and Upcase () to resolved ur problem?

thanks in advance
fareeda

[fareeda_b]

HI SujathaK,

Could you please give me xplanation how you used the Downcase () and Upcase () to resolved ur problem?

thanks in advance
fareeda

[chulett]

You only need one or the other, not both:

AND UPCASE(VALUE) = 'XXX'

-or-

AND DOWNCASE(VALUE) = 'xxx'