Leading zeros are being stripped when using the Advanced Query Command
Question
The leading zeros in my code are getting stripped with the Advanced Query Command. How do I prevent this from happening?
Answer
The Advanced Query will try to automatically determine the value type of the column. In this case, it recognized the code as integer and as a result will strip/ignoring the leading 0s. Here is a workaround to prevent this from happing:
- Use the OneCloud Tabular Transformation BizApp and select the "Find and Replace" Command.
- Step 1: Add single quotes
Replace that column with the value surrounded by single quotes using Regular Expressions (Regex).- Search for RegEX: (\d+)
- Replace with : '$1'
- Check "Replaces Matches Only"
- Step 2: Remove single quotes
Once the Advanced Query is complete, then remove the quotes.- Search for RegEX: '(\d+)'
- Replace with : $1
- Check "Replaces Matches Only"
- Step 1: Add single quotes
Note: This RegEx uses the parenthesis to "capture" a group of characters. These characters are then replaced as the first parameter $1. Multiple captures can be created with subsequent sets of parenthesis and accordingly incremental capture values such as $2.
Alternative Approach:
As an alternative approach, you can use the PRINTF function to pad the data on the returned recordset. Here is an example that will pad to 8 characters --> select printf('%08d',COL1) as COL1 from DATA
If you need additional support, please contact support@onecloud.io.