Inserting a integer in sql from one digit to three digit

Question

1.00/5 (1 vote)

See more:

Hi i'd like to know one thing. i have a column called nos in a table. now i need to insert no s from 1 to 999 in tat column. if i insert 1 it should save as 001 and if i insert 34 it should save as 034. i want to declare this while specify the column s only. i dont want to run a seperate update statement.is there any way for this?

Posted 7-Aug-13 1:30am

ARUN K P

Updated 7-Aug-13 1:34am

Thanks7872

v2

Add a Solution

Comments

Maciej Los 7-Aug-13 7:45am

Why do you want to add leading zeros? What if nos will be greater than 999?

ARUN K P 7-Aug-13 8:41am

The requirement is like tat it should be 3 digits only. u cant insert more than 3 digits. tats why.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhinav S · Accepted Answer · 2013-08-07T01:36:00

Solution 1

You cannot do this with integer or any numeric type - you need to use varchar or the database equivalent.

Posted 7-Aug-13 1:36am

Abhinav S

Comments

Maciej Los 7-Aug-13 7:46am

Agree, but is it good practice? See my comment to the question.

Abhinav S 7-Aug-13 7:51am

Yeah. This is perhaps something taht should be done at display (UI) level rather than at database level.

Mukesh Ghosh · Accepted Answer · 2013-08-07T01:49:00

Solution 2

Try like

SQL

SELECT TOP 10 RIGHT('0000000'
+ CAST(TrnID AS VARCHAR(7)), 7) PaddedContactID
FROM tbl_Trn_Payments
GO

Posted 7-Aug-13 1:49am

Mukesh Ghosh

Comments

ARUN K P 7-Aug-13 8:44am

ok mukesh. is there any way to specify that condition while declaring that column while creating table. like create table( no varchar(30)) . is there any way tat we could specify tat condition in create table statement?