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
-
lpad()andrpad()are not trimming functions, they are padding functions. I triedrpad()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) -
Also, note the difference in quotation marks. You must use
""instead of ""
Content aside
- 5 yrs agoLast active
- 2Replies
- 1408Views