Learn how to a build a cloud-first strategyRegister Now


left zero and int data type

Posted on 2009-12-30
Medium Priority
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

Mrunal earned 668 total points
ID: 26145204

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

tigin44 earned 668 total points
ID: 26145261
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
Anthony Perkins earned 664 total points
ID: 26151644
>>can I do that where data type still int not else?<<

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Integration Management Part 2
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

810 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