Length issue

[scorpion]

Hi All,

Could any one help me to solve below request:

I have a input column in my input table like 'XXX'.

I need to map this to an output column as per below reqrmnt:

if length of column XXX < 9 then need output like 'AAA'

Else out put like (7-9)of the xxx COLUMN VALUE.


Tx in advance

[Maveric]

7-9 as in the substring of XXX? And "length of column" as in field length or data length? if first is yes and second is data length see if this helps.

If Len(XXX) <9 Then 'AAA' Else XXX[7,3]

[sas]

Maveric solution will work based on the assumption that column data type is CHAR not VARCHAR.

[kumar_s]

Position based transformation logic always works well and predictable with Fixed length or as mentintioned Char datatype in Datastage.

[scorpion]

hi Mavrec/sas

Thanks for the reply,but both my input and output columns are varchar.

So how to proceed in this case?

[kumar_s]

Its not that the given logic doesn't work for Varchar type data. It is assumed that if the data contains trailing empty space will be truncated with the Varchar datatype.
Based on the given condition, it is expected to have varying field length value for that field. Its ok to have the given condition with the Varchar field. Infact CHAR will not help you in the current scenario, as Len(field) will always return the length of the field.

[Maveric]

Like Kumar_s Said if the field is a varchar then you will have to use trimleading trailing function to strip all spaces.