left zero and int data type

Posted on 2009-12-30
Last Modified: 2012-05-08
I have a column in SQL 2005 with data type int I want to insert into this column values like (0000,0001,0002,0003,.....) but SQL trim left zero and insert values like (0,1,2,3,...), can I do that where data type still int not else?
Question by:hazemfadl
    LVL 22

    Accepted Solution


    Its sql server built in feature of doing this.
    To do same you have to either change data type or insert as it is and while showing / retrieving it add zeros.

    try this:
    select right(replicate('0',4)+ convert(varchar(4),CityID),4) from Cities

    Hope this solves your problem.
    LVL 26

    Assisted Solution

    no you cant do that since your data type is int. You should change it to varchar or use a function that will add the trailing zeros to the number and use that function to get the values as in the format that you deserve...
    LVL 75

    Assisted Solution

    by:Anthony Perkins
    >>can I do that where data type still int not else?<<

    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

    Suggested Solutions

    Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
    I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    731 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

    15 Experts available now in Live!

    Get 1:1 Help Now