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.
Len function issues in VARCHAR columns
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
Len function issues in VARCHAR columns
Input records are as follows,
TRSCX
TSRPX
TRCPX
TRGIX
W146#
TIKRX
TIIEX
W146#
TRRSX
TIHRX
TIKRX
TRCPX
TRSCX
TIQRX
TRIEX
TRSCX
TSRPX
TRCPX
TRGIX
W146#
TIKRX
TIIEX
W146#
TRRSX
TIHRX
TIKRX
TRCPX
TRSCX
TIQRX
TRIEX
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?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 187
- Joined: Thu Apr 14, 2011 5:10 pm
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.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.
Is there any other function that can get the exact length value of a string which contains special characters.
Thanks
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>