Len function issues in VARCHAR columns

siva7143 · Post by **siva7143** » Tue Oct 22, 2013 8:43 am

Hi,

Need to load the source records which must be 5 or 6 characters long into the target table.

Source field Data Type - VARCHAR (15) NULLABLE column
Target field Data Type - VARCHAR (15) NULLABLE column

When I use the below function,
If (Len(Trim(InputColumn)) = 5 Or Len(Trim(InputColumn)) = 6) Then 1 Else 0

Records with length of 5 are not loaded into the target, where as they're loaded when I change the Len function as
If (Len(Trim(InputColumn)) = 10) Then 1 Else 0

Please let me know the possible resolution for this issue.

Thanks.

priyadarshikunal · Post by **priyadarshikunal** » Tue Oct 22, 2013 8:56 am

Can you try looking at the hex values of data in that field? If it is a file you can try any hex editor to check if there are some trailing non-printable character.

siva7143 · Post by **siva7143** » Tue Oct 22, 2013 9:32 am

Input records are as follows,
TRSCX
TSRPX
TRCPX
TRGIX
W146#
TIKRX
TIIEX
W146#
TRRSX
TIHRX
TIKRX
TRCPX
TRSCX
TIQRX
TRIEX

ArndW · Post by **ArndW** » Tue Oct 22, 2013 9:52 am

In addition to the list of values, could you output the numer value of "LEN(TRIM(<columnname>))" as well so we can identify which ones don't meet your expectations?

Developer9 · Post by **Developer9** » Tue Oct 22, 2013 9:57 am

Hi,

Tried with this statement

If (Len(Trim(InputColumn)) = 5 Or Len(Trim(InputColumn)) = 6) Then 1 Else InputColumn=0

Have Constraints in the transformer (InputColumn=0 OR 1) to verify the output

Thanks.

chulett · Post by **chulett** » Tue Oct 22, 2013 10:00 am

Neither of those expressions are valid:

If (Len(Trim(InputColumn)) = 5 Or Len(Trim(InputColumn)) = 6) Then 1 Else 0

InputColumn=0 OR InputColumn=1

siva7143 · Post by **siva7143** » Tue Oct 22, 2013 10:01 am

Developer9 wrote:Hi,

Tried with this statement

If (Len(Trim(InputColumn)) = 5 Or Len(Trim(InputColumn)) = 6) Then 1 Else InputColumn=0

Have Constraints in the transformer (InputColumn=0 OR 1) to verify the output

Thanks.

Tried that option as well, seems like we have some special charaters coming from source which we are not able to read that in Datastage.
Is there any other function that can get the exact length value of a string which contains special characters.

Thanks

ArndW · Post by **ArndW** » Tue Oct 22, 2013 10:02 am

Using what you've done it seems that strings that you think should have a length of 5 or 6 don't; but now you should try to find out what length those strings actually have (according to the LEN() function). The characters don't look like multibyte ones, but there might be hidden and non-displayable characters mixed in there.