Skip to Content

      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:

LPAD.jpg

  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:

LPAD-RPAD.jpg

             

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.

  PAD Output.jpg

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. sree o

    It worked well lpad and rpad for the column ENAME but not working for the column EMPNO.

    I have taken the same above example that source as an excel sheet containing the below data.

    emp_xls.JPG

    mapped lpad( EMP.EMPNO, 10, ‘0’) and rpad( EMP.EMPNO, 10, ‘0’) in query transformation

    query.JPG

    Output:

    output.JPG

    in the above output lpad and rpad not applied for the coumn EMPNO.

    and I have taken datatype as varchar(255) for both columns. please help me to get for empno also. I want to display 10 chars value for empno like 0000007001, 0000007002,….

    But its working fine if empno contains any value chars(e.g.”test”) not digits.

    (0) 
  2. D K

    Yes … Sree its behaving in a same way ….i dont its a bug or its functionality is like that.

    (0) 
  3. rajalakshmi thanikesan

    Good Example.

    adding to this, to be simple,

    LPAd – is to add the strings / characters to the left of the word

    RPAD – is to add the strings / characters to the right of the word

    Regards,

    Raji

    (0) 

Leave a Reply