• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 546
  • Last Modified:

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
0
akatz66
Asked:
akatz66
1 Solution
 
BeartlaoiCommented:
Will this expression work for you?
('1212' + REPLICATE('0', 8-LEN(ID_NUMBER)) + ID_NUMBER)

Open in new window

0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now