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"

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.