Enter formated data in sql like 001,003,087,234

Question

1.00/5 (2 votes)

See more:

I want to store data in sql as following format

0001
0002
0003
0004
.
.
.
.
9999

my table is

create table tblAssest (code nvarchar(4))

waiting for a positive answer........

Posted 30-Aug-12 23:49pm

Kailash_Singh

Add a Solution

3 solutions

Solution 2

Here is one approach

SQL

--create table tblAssest (code nvarchar(4))

DECLARE @InitialCount INT
DECLARE @FinalCount INT

SET @InitialCount = 1
SET @FinalCount = 9999

WHILE  @InitialCount <= @FinalCount
BEGIN

    INSERT INTO tblAssest
    SELECT RIGHT('000' + CAST(@InitialCount AS VARCHAR),4)

    SET @InitialCount = @InitialCount + 1

END

SELECT * FROM tblAssest

--DROP TABLE tblAssest

Posted 30-Aug-12 23:58pm

__TR__

Solution 1

Assuming that you have to use just T-SQL, here are some good approaches: http://sqlusa.com/bestpractices2005/padleadingzeros/[^], http://stackoverflow.com/questions/121864/most-efficient-t-sql-way-to-pad-a-varchar-on-the-left-to-a-certain-length[^]

Posted 30-Aug-12 23:52pm

Zoltán Zörgő

Updated 30-Aug-12 23:53pm

v2

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)

Wendelius · Accepted Answer · 2012-08-31T10:06:00

A bit different answer. If you're actually storing numbers, don't format them in a table, never! Instead store them as numbers and handle all the formatting requirements on the client side.

The database is meant to store... well the data. Not the formatted values. The best results are achieved when the data is in it's natural form and when the data is represented to the user it's formatted properly using sufficient information about the client.