Leading 0s and concatenation

Posted on 2012-09-20
Last Modified: 2013-11-11
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.

Question by:akatz66
    1 Comment
    LVL 9

    Accepted Solution

    Will this expression work for you?
    ('1212' + REPLICATE('0', 8-LEN(ID_NUMBER)) + ID_NUMBER)

    Open in new window


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment’s Console is a great tool to view activities, leads, contacts, accounts and opportunities all in one screen. It is particularly effective during call blocks and working numerous activities at a time in a quick, repetitive fashion (suc… is a cloud-based customer relationship management (CRM) system. In this article, you will learn how to add and map custom lead and contact fields to your Salesforce instance.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now