Tuesday, August 27, 2013

LPAD & RPAD Implementation in OBIEE

LPAD & RPAD Implementation in OBIEE

 FOR LPAD
CASE WHEN N<=LENGTH(TABLE1.COL1) THEN SUBSTRING(TABLE1.COL1 FROM 1 FOR N) ELSE CONCAT(REPEAT(' CHARACTER ', N-LENGTH(TABLE1.COL1)), TABLE1.COL1) END

THE STRING IS LEFT PADDED TO LENGTH N WITH CHARACTER.

FOR RPAD 
CASE WHEN N<=LENGTH(TABLE1.COL1) THEN SUBSTRING(TABLE1.COL1 FROM 1 FOR N) ELSE CONCAT(TABLE1.COL1, REPEAT('CHARACTER ', N-LENGTH(TABLE1.COL1)) END

THE STRING IS RIGHT PADDED TO LENGTH N WITH CHARACTER


Example LPAD& RPAD:
Suppose office shift time as follows , Shift_In:800, Shift_Out:1200 so here Shift_in time is three characters & Shift_out time is four characters. Sometime Shift_In time may be more than three characters.

Following is the way we can handle such LPAD & RPAD scenario.

CASE
WHEN LENGTH(CAST(("Dim_OFF_SHIFT_TIME"."SHIFT_IN_TIME") AS CHAR)) =3 THEN  
SUBSTRING(CAST("Dim_OFF_SHIFT_TIME"."SHIFT_IN_TIME" AS CHAR) FROM 1 FOR 1)||':'|| SUBSTRING
(CAST("Dim_OFF_SHIFT_TIME"."SHIFT_IN_TIME" AS CHAR) FROM 2 FOR 3)

WHEN LENGTH(CAST(("Dim_OFF_SHIFT_TIME"."SHIFT_IN_TIME") AS CHAR))=4 THEN  
SUBSTRING(CAST("Dim_OFF_SHIFT_TIME"."SHIFT_IN_TIME" AS CHAR) FROM 1 FOR 2)||':'|| SUBSTRING
(CAST("Dim_OFF_SHIFT_TIME"."SHIFT_IN_TIME" AS CHAR) FROM 3 FOR 4)

ELSE
CAST(("Dim_OFF_SHIFT_TIME"."SHIFT_IN_TIME") AS CHAR)
END
 
Posted by at
Labels:

2 comments:

  1. UnknownMay 12, 2016 at 11:06 AM

    This works fine and is shorter for RPAD:
    SUBSTRING(TABLE1.COL1||REPEAT('CHARACTER ', N) FROM 1 FOR N)

    ReplyDelete
  2. STrudeauDecember 12, 2016 at 9:32 AM

    I know this is an old post, but I found an easier way to pad the string in OBIEE.

    right('0000' || cast("Market Information"."Market Number" as VARCHAR(100)),4)

    This ensures that every field is 4 characters and has leading zeros on anything that is less than 4 characters

    ReplyDelete

Subscribe to: Post Comments (Atom)