Adding Leading Zeros To A Field Value With TIBCO Scribe® Online
Article ID: KB0078327
Updated On:
| Products | Versions |
|---|---|
| TIBCO Cloud Integration - Connect ( Scribe ) | - |
Description
When mapping data between a source and target datastore, you can use a formula to modify source field values so all values are the same length or number of characters when added to the target.
Use a combination of the IF, LEN, TRIM, and PADL functions in a formula to add leading zeros to the beginning of a number.
NOTE: For some source datatypes, the LEN function returns the length of the data field instead of the number of characters in the value. If an nvarchar(10) field contains the value 123, then LEN may return 10 instead of 3. In this case, use the TRIM function to remove spaces, except spaces between words from the text.
The formula below uses functions to add leading zeros to a number to make that number five digits long, as shown in the From/To conversion table.
IF( LEN(TRIM(S2)) < 5, PADL(TRIM(S2), 5, ‘0’), S2)
NOTE: The PADL function is only available in TIBCO Scribe® Online.
You can use the same set of functions to convert numbers to numbers with preceding identifiers, such as PO or SO, to indicate Purchase Order or Sales Order..
The formulas below use functions to add leading zeros to a number to make that number five digits long, and to add the preceding identifier, SO, as shown in the From/To conversion table. The formula below uses functions to add leading zeros to any number, less than 5 digits, and to add the preceding identifier, SO, as shown in the From/To conversion table.
IF( LEN(TRIM(S2)) < 6, "SO" + PADL(TRIM(S2), 5, ‘0’), S2)
For additional information see the following in TIBCO Scribe® Online Help: Formula Editor, IF Function, LEN Function, TRIM Function, PADL Function
Example 1
To convert the field values below:| From | To |
|---|---|
| 1 | 00001 |
| 12 | 00012 |
| 123 | 00123 |
| 1234 | 01234 |
| 12345 | 12345 |
Use a combination of the IF, LEN, TRIM, and PADL functions in a formula to add leading zeros to the beginning of a number.
NOTE: For some source datatypes, the LEN function returns the length of the data field instead of the number of characters in the value. If an nvarchar(10) field contains the value 123, then LEN may return 10 instead of 3. In this case, use the TRIM function to remove spaces, except spaces between words from the text.
The formula below uses functions to add leading zeros to a number to make that number five digits long, as shown in the From/To conversion table.
IF( LEN(TRIM(S2)) < 5, PADL(TRIM(S2), 5, ‘0’), S2)
NOTE: The PADL function is only available in TIBCO Scribe® Online.
You can use the same set of functions to convert numbers to numbers with preceding identifiers, such as PO or SO, to indicate Purchase Order or Sales Order..
Example 2
To convert the field values below:
| From | To |
|---|---|
| 1 | SO00001 |
| 12 | SO00012 |
| 123 | SO00123 |
| 1234 | SO01234 |
| 12345 | SO12345 |
The formulas below use functions to add leading zeros to a number to make that number five digits long, and to add the preceding identifier, SO, as shown in the From/To conversion table. The formula below uses functions to add leading zeros to any number, less than 5 digits, and to add the preceding identifier, SO, as shown in the From/To conversion table.
IF( LEN(TRIM(S2)) < 6, "SO" + PADL(TRIM(S2), 5, ‘0’), S2)
For additional information see the following in TIBCO Scribe® Online Help: Formula Editor, IF Function, LEN Function, TRIM Function, PADL Function
Issue/Introduction
Use a formula to modify field values.
Feedback
Yes
No
Powered by
