Help Desk

Submit a ticket My Tickets

Leading zeros are being stripped when using the Advanced Query Command

The leading zeros in my code are getting stripped with the Advanced Query Command. How do I prevent this from happening?

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

Login to post a comment