Adding Leading Zeros To A Field Value With Scribe Insight
Article ID: KB0078673
Updated On:
| Products | Versions |
|---|---|
| TIBCO Scribe Insight | 7.9.1 |
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 REPT 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 formulas below use 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, REPT("0", 5 - LEN(TRIM(S2))) & TRIM(S2), S2)
NOTE: The REPT function is only available in Scribe Insight.
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 formula below uses 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" & REPT("0", 5 - LEN(TRIM(S2))) & TRIM(S2), S2)
For additional information see the following in Scribe Insight Help: Formula Editor, Function Reference, IF Function, LEN Function, TRIM Function, REPT 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 REPT 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 formulas below use 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, REPT("0", 5 - LEN(TRIM(S2))) & TRIM(S2), S2)
NOTE: The REPT function is only available in Scribe Insight.
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 formula below uses 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" & REPT("0", 5 - LEN(TRIM(S2))) & TRIM(S2), S2)
For additional information see the following in Scribe Insight Help: Formula Editor, Function Reference, IF Function, LEN Function, TRIM Function, REPT Function
Issue/Introduction
Modify field values using Scribe Insight.
Feedback
Yes
No
Powered by
