akatz66
asked on
Leading 0s and concatenation
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.
For example
Table 1 Table 2
505050 121200505050
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.
Thanks,
Andy
For example
Table 1 Table 2
505050 121200505050
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.
Thanks,
Andy
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.