Advanced Query uses a SQLite database to perform its operations. The CSV file specified in the Tables section of the Command automatically generates a table. When the table is generated, the field types are specified as affinity numeric. This can sometimes be problematic because fields that resemble numbers may need to be treated as text since certain row values may have leading zeros.

As an example, a U.S. zip code is five digits. The states of Connecticut, Massachusetts, Maine, New Hampshire, New Jersey, Rhode Island, and Vermont have zip codes that begin with a zero. When Advanced Query imports data into the zip code field, it will generally evaluate the zip code as a number and remove the leading zero.

The Output generated by the Advanced Query Command can be modified to account for these instances by using various supported SQLite database functions. The most common to address this need are the substr and printf functions.

SUBSTR

This SQLlite function returns a substring of a field value. A simple way to utilize this function is to simply concatenate (double pipes - ||) a string of zeros that represents the total number of characters expected in the field value with the actual field value. The number of characters parameter of the function can be specified as the negative value of the total characters. Using a negative character value instructs the substring function to return the last X number of characters from the combined string.

substr('00000' || zip_code,-5)

PRINTF

The SQLlite function formats the field value that is returned by the query. A simple way to utilize this function is to specify the format of field values as a decimal.

printf('%05d',zip_code)

When to use SUBSTR vs PRINTF?

  • The SUBSTR function should be used when the field contains numeric and text values.

  • This PRINTF function is generally recommended to be used on fields that contain all numeric values.

Example

The below displays the CSV data used in the Advanced Query Command, the use of these functions in the query defined in the Command, and the resulting Output. The yellow highlight represents field value before any functions while the red and orange represent the substr and printf functions, respectively.

Data

Query

Results


Supporting Materials

The below can be used to test this solution within your OneCloud Integration Studio environment. Simply copy the data to a text file and save it as a Workspace Resource. This Resource can then be used in the Advanced Query Command Tables section and the query below in the Query section of the Command.

These functions can also be tested in a database client like DBeaver.

Data

zip_code
10036
02110
90210

Query

Select 
zip_code
,substr('00000' || zip_code,-5) as [substr_zip_code]
,printf('%05d',zip_code) as [printf_zip_code]
from data

Did this answer your question?