0

How to use rpad() and lpad()

I have 2 fields named LastName and SocialSecurity. I am making an ID field using the first four letter of their last name and the last 4 letter of the SS #.

For example  Jones     123456789  would end up JOHN6789

My problem occurs only if the last name is shorter than 4 characters.

For Example   Whu     123456789  should end up WHU-6789 and

                     Yu        123456789  should end up Yu--6789

I was using the rpad('last name',3,"-")  to enter the last name portion of the ID.  But this did nothing to the last name.

I even tried lpad but this made no difference.

In fact when the name was Wright, it printed Wright6789.

What would be the full line of code to get what I need.

2 replies

null
    • Sean
    • 4 yrs ago
    • Reported - view

    lpad() and rpad() are not trimming functions, they are padding functions. I tried rpad() like you show in your example with "Yu" and it worked fine. I did use 4 instead of 3 though. The following code will trim, pad and concatenate the fields to give you the result you want...

     

    rpad(substr(LastName, 0, 4), 4, "-") + substr(SSN, 5)

    • Sean
    • 4 yrs ago
    • Reported - view

    Also, note the difference in quotation marks. You must use "" instead of ""

Content aside

  • 4 yrs agoLast active
  • 2Replies
  • 1394Views