Question: We have a string field that has an alpha character as the first character for one client or just contains numbers for other clients. How do I test in the map if that first character is any letter of the alphabet?
Here's how you can accomplish this:
As an example, suppose we have a field on the source data called F1 which is being mapped to target field called CarType.
Now if F1 contains an alpha character in position 1 of this value then I need to just send the numeric and not the character, if the field only contains numeric value then just send that value to the Target element. So basically we have to figure out if the first character is alpha or not.
In the below snapshot, I'm using the following:
A. Use Substring to get the first character of F1 starting from position 1. Thus it will have parameters F1, 1, 1 respectively.
B Now the value of this Substring is going as a first parameter to Translation function.
C. Now let's look at area marked as 1 in red. This is a Constant function which can have value 'A,B,C,D....Z'. You can also include lower-case alphabets separated by commas.
D. This Constant is going as second parameter to Translation (area marked as 2 in red).
E. The Third parameter of Translation is '' (two single quotes) which means blank. So we are saying if any alpha character is in position 1 of F1 then replace it with null.
F. Now we have to put our When/OtherWise condition.
- if the first character is != '' then the value is F1 (which means it is numeric only value).
- else get the substring (marked as 3 in red) which has the start position 2 and end position is the Length (value can have dynamic length, so I used that here).
D. The output of When/Otherwise is then mapped to Target field.
If you want to just find out if the first character is alpha or not, then in When/Otherwise function, the second parameter can be a constant value called 'False' and the third parameter as constant value 'True'.
The boolean condition and its incoming parameters (as explained in step A to E) will remain as is.