2 Replies Latest reply on Feb 26, 2014 1:49 AM by deepak.joshi0522@gmail.com

    Advanced Info on RPAD/LPAD

    Guru

      Hi All,

       

      Here i want to share some of the improvement which i have used in my project ..many of the experts might have used but surely helpful to beginers.

       

      Let me tell you with Business case which forced me to explore on RPAD and LPAD.

       

      Business case: As part of loading to target system(Delimeted falt files) we were forced to pad spaces or 0 as per the length for the column.

       

      for ex: IND_GRP_CODE with length 3. we need in Target as 3 digit number irrespective of the didgits coming from source.

      If source is  12  than it should be 012 ... If it is 1 than 001 Incase if the source value is Null also we need 000.

       

      Initially i have used LPAD(IND_GRP_CODE,3,'0')

       

      But the above expression has limitation..Since RPAD returns null incase the Input is  NULL.

       

      Than we wrote it as IIF(ISNULL(IND_GRP_CODE),'000',LPAD(IND_GRP_CODE,3,'0'))

       

      But even the above one is good for small values incase a string of 120 ..we need to give 120 spaces ..that is very cumbersome..

       

      The best expression for the above scenario would be :

       

      LPAD(IIF(ISNULL(IND_GRP_CODE),'0',IND_GRP_CODE),3,'0')  to pad "0"

       

      LPAD(IIF(ISNULL(INDUSTRY_DESC),' ',CINDUSTRY_DESC),120,' ') to pad spaces . Similarly for RPAD also.

       

      All you need to do is change the column name and length's thats it.It will handle the nulls and you need to give any default value like 000000000 for a 9 digit value.

       

      Hope it is helpful.

       

      Regards

      Shirish