How to Concatenate and Left Pad (Oracle SQL LPAD) Strings in Unica Campaign Flowchart
A question was asked on IBM's Unica Campaign developerWorks how one could concatenate different numbers and strings such that some of the numbers were left padded.
Unfortunately Unica Campaign Macros do not have a simple "LPAD" function; therefore, there are two ways to solve this problem:
Use STRING_CONCAT and define your own LPAD Unica Macro
Use STRING_CONCAT and use Unica Macro FORMAT
To make this example more readable I am going to modify the request by adding underscores as a requirement between the concatenated fields:
Example Output:
7568021506373990_02798_000000001_00412_00000000008_67741_0064135452014082_000000402|000000316800
Generalized Output:
<landcode>_<postcode>_<Datamix>_<RefNummer>_<Treatmentcode>_<Sendungsnummer>_<ID_Absender>_<Kundendaten>
(Kundendaten is composed from other fields)
The Main Question is:
How Does One Generate an Left Padded (zero fill to the left with n digits/characters)?
In other words, how does one turn "1" into "000000001"?
To Start:
The generalized function for padding is:
FORMAT(<FIELD_NAME>,<STRING_LENGTH>,<PRECISION>,<FORMAT_TYPE>,<FIELD_PULL_DIRECTION>,<PADDING_FILLER>)
In the following set, I will assume that we are dealing with 10 character string lengths, and will leave it to the user to either remove, or modify the padding if needed.
FORMAT(landcode,10,0,ZERO,RIGHT,ZERO)
FORMAT(postcode,10,0,ZERO,RIGHT,ZERO)
FORMAT(Datamix,10,0,ZERO,RIGHT,ZERO)
FORMAT(RefNummer,10,0,ZERO,RIGHT,ZERO)
FORMAT(Treatmentcode,10,0,ZERO,RIGHT,ZERO)
FORMAT(Sendungsnummer,10,0,ZERO,RIGHT,ZERO)
FORMAT(ID_Absender,10,0,ZERO,RIGHT,ZERO)
FORMAT(Kundendaten,10,0,ZERO,RIGHT,ZERO)
After the individual items have been formatted the user can then concatenate (STRING_CONCAT) them together:
STRING_CONCAT( FORMAT(landcode,10,0,ZERO,RIGHT,ZERO), '_', FORMAT(postcode,10,0,ZERO,RIGHT,ZERO), '_', FORMAT(Datamix,10,0,ZERO,RIGHT,ZERO), '_', FORMAT(RefNummer,10,0,ZERO,RIGHT,ZERO), '_', FORMAT(Treatmentcode,10,0,ZERO,RIGHT,ZERO), '_', FORMAT(Sendungsnummer,10,0,ZERO,RIGHT,ZERO), '_', FORMAT(ID_Absender,10,0,ZERO,RIGHT,ZERO), '_', FORMAT(Kundendaten,10,0,ZERO,RIGHT,ZERO) )
Thanks to one of my coworkers for helping me troubleshoot this