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
akatz66Asked:
Who is Participating?
 
BeartlaoiConnect With a Mentor Commented:
Will this expression work for you?
('1212' + REPLICATE('0', 8-LEN(ID_NUMBER)) + ID_NUMBER)

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.