Page 1 of 1
Len function issues in VARCHAR columns
Posted: Tue Oct 22, 2013 8:43 am
by siva7143
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.
Posted: Tue Oct 22, 2013 8:56 am
by priyadarshikunal
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.
Len function issues in VARCHAR columns
Posted: Tue Oct 22, 2013 9:32 am
by siva7143
Input records are as follows,
TRSCX
TSRPX
TRCPX
TRGIX
W146#
TIKRX
TIIEX
W146#
TRRSX
TIHRX
TIKRX
TRCPX
TRSCX
TIQRX
TRIEX
Posted: Tue Oct 22, 2013 9:52 am
by ArndW
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?
Posted: Tue Oct 22, 2013 9:57 am
by Developer9
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.
Posted: Tue Oct 22, 2013 10:00 am
by chulett
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
Posted: Tue Oct 22, 2013 10:01 am
by siva7143
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
Posted: Tue Oct 22, 2013 10:02 am
by ArndW
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.