Leading 0s and concatenation
Posted on 2012-09-20
Hi all, I am trying to get my sql code to add leading 0s to certain fields and then add other digits to the front. I am trying to match an 8 digit code with a 12 digit code from another table. The 8 digit code however, could be 5, 6, 7, or 8 digits.
Table 1 Table 2
So in this case, I would first want the 505050 to be 00505050. Essentially I want leading 0s in front of the number so it has 8 digits. For codes that are already 8 digits, it shouldn't do anything.
So the steps would be
1. turn 505050 to 00505050
2. add 1212 to the front of the 00505050
If you want a field name, table 1 would be ID_NUMBER and table to would be HOME_ID.
eventually I will be joining the two tables using a.ID_NUMBER=b.HOME_ID.