How to use the LPAD & RPAD formatting function in BODS

[Former Member]

      Purpose of this blog is to explain the padding function within BO Data Services with example. The padding function is used for formatting the string. The two extensively used padding functions within BODS are:

1. LPAD:
   Pads the string with characters in the left from a given pattern. Meaning if the user wants the input string (may be any database column of type string) to be of specific number of characters (e.g.50) which should be appended by any specific character (e.g. ‘0’) at the left then the LPAD function can be used. The LPAD function repeats the pattern at the end of the input string until the final string is of the appropriate mentioned length. If the input string is already longer than the expected length, this function truncates the string.
2. RPAD:
   Pads the string with characters in the right from a given pattern. Meaning if the user wants the input string (may be any database column of type string) to be of specific number of characters (e.g.50) which should be appended by any specific character (e.g. ‘0’) at the right then the RPAD function can be used. The RPAD function repeats the pattern at the end of the input string until the final string is of the appropriate mentioned length. If the input string is already longer than the expected length, this function truncates the string.

Syntax of LPAD and RPAD function within BODS:

  Let us take an example and see how the LPAD and RPAD functions work. Steps to be followed:

1. Login to the BO Data services designer.
2. Create a test Project say PRJ_TEST.
3. Create a test Job say TEST_JOB.
4. Create a Workflow say WF1 (This step is optional, user can directly place a Dataflow).
5. Drag and drop a Dataflow, and name it as DF_LPAD_RPAD.
6. Take a source table from any data store or any flat file. In the example an Excel file is
   taken as source with 2 columns EMP_NO, EMP_NAME. Note: we will apply padding on
   EMP_NAME.
7. Import the excel file in the BO Data services designer.
8. Drag and drop the Excel file & a query transform on the data flow.
9. Create a Temporary table say LR_PADDING (as target), into the data store configured on
   user’s system.
10. The below screen shows the usage of LPAD function:

The syntax in this example for LPAD and RPAD is as follows:

            Lpad (Query_1.EMP_NAME,10,'0')

Rpad (Query_1.EMP_NAME,10,'0')

11.Validate the job and then execute the job.

12.Check the resultant data. The below screen shot displays the output of the target table,where Padding is applied, where EMP_NAME column is the original value from the exce file and LPAD_EMPNO & RAPD_EMPNO are the values after applying the padding formatting function.