Help Desk

Submit a ticket My Tickets
Welcome
Login

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.

Login to post a comment