Add characters to sql server field

Posted on 2004-11-23
Last Modified: 2006-11-17
I have a field in sql server that is 6 characters long but the data can range from 1 - 6 characters in length.  I want to add '0' to the field where the length is less than 6. Say if the field contains 2003 I want to replace it with 002003 same goes for 52 I want to replace it with 000052.  Any ideas would be greatly appreciated.

Question by:tbailey922
    LVL 16

    Accepted Solution

    UPDATE Table
    SET Field = RIGHT('000000' + RTRIM(LTRIM(Field)), 6)

    Author Comment



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    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

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    761 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

    8 Experts available now in Live!

    Get 1:1 Help Now