key column from 0000001 to 9999999

[vbnetrookie]

Hi all,

I want to add a column to my db table with numbers starting at 0000001
and going t'ill the end...It has 2.7 millions entries..so it should be
around .. 2700000. I really need the first number to have all the extra
'0's in front...whenever I place a key column with an identity..it
starts a 1 and increments...I need it to start at 0000001. I tried
placing that(0000001) in the identity seed and increment by 1 but it
still start at 1. I'm fairly a begginer in sql server 2000 but can
manage my way around...
I also tried a query:

alter table dbo.tablename
add column columnname int not null
identity(0000001,1)

and that didn't work out either.(just starts at 1)
So if someone could help me out here I would appreciate. Thanks again
for all the help!!
JMT

[David Portas]

You are making the double mistake of A) assigning some business meaning
to an artificial IDENTITY key and B) performing formatting in the
database rather than in the client or display tier.

Store the value as a number and forget about how it's formatted (you
can always do it in a view if you must) or use a CHAR / VARCHAR column
and assign a meaningful key rather than generate one artificially.

--
David Portas
SQL Server MVP
--

[David Portas]

You are making the double mistake of A) assigning some business meaning
to an artificial IDENTITY key and B) performing formatting in the
database rather than in the client or display tier.

Store the value as a number and forget about how it's formatted (you
can always do it in a view if you must) or use a CHAR / VARCHAR column
and assign a meaningful key rather than generate one artificially.

--
David Portas
SQL Server MVP
--

[vbnetrookie]

This is just for a prototype that I'm building. I don't really care who
gets what number and in what order but I need a column that has 7
digits for every row and every row must have a different number. Is
this possible ?? My column is already a VARCHAR.
I am just wondering if it's possible to do something like this ??
I understand that you're trying to teach me something that would be
better for databases in general but I really just need this to work...
THanks alot for the reply!
JMT

[vbnetrookie]

This is just for a prototype that I'm building. I don't really care who
gets what number and in what order but I need a column that has 7
digits for every row and every row must have a different number. Is
this possible ?? My column is already a VARCHAR.
I am just wondering if it's possible to do something like this ??
I understand that you're trying to teach me something that would be
better for databases in general but I really just need this to work...
THanks alot for the reply!
JMT

[David Portas]

If this is just a one-off:

DECLARE @x VARCHAR(7)
SET @x = 0

UPDATE YourTable
SET @x = col = RIGHT('0000000'+CAST(@x + 1 AS VARCHAR),7)

This is undefined behaviour so don't rely on it in any persistent code.

--
David Portas
SQL Server MVP
--

[David Portas]

If this is just a one-off:

DECLARE @x VARCHAR(7)
SET @x = 0

UPDATE YourTable
SET @x = col = RIGHT('0000000'+CAST(@x + 1 AS VARCHAR),7)

This is undefined behaviour so don't rely on it in any persistent code.

--
David Portas
SQL Server MVP
--

[Mark D Powell]

I would like to know how to do this in SQL Server also. I scanned the
online documentation but I didn't find a useful example. In Oracle
this is real easy:

UT1 > select to_char(123,'000009') as CNUM from dual;

CNUM
-------
000123

I have got to believe that there is a fairly simple way to do this in
SQL Server via a couple of provided functions but I haven't been able
to figure it out yet looking at CONVERT and STR. Who can save me a
couple of hours?

-- Mark D Powell --

[vbnetrookie]

Thanks alot David, this does the job just fine. It was just what I was
looking for...

THanks again,
JMT

[Erland Sommarskog]

vbnetrookie (bi****@hotmail.com) writes:

This is just for a prototype that I'm building. I don't really care who
gets what number and in what order but I need a column that has 7
digits for every row and every row must have a different number. Is
this possible ?? My column is already a VARCHAR.
I am just wondering if it's possible to do something like this ??
I understand that you're trying to teach me something that would be
better for databases in general but I really just need this to work...
THanks alot for the reply!

This is quite easy actually. Drop your varchar column as it is now. Then
say:

ALTER TABLE tbl ADD
ident int IDENTITY,
displaykey AS RIGHT('0000000'+CAST(ident + 1 AS VARCHAR),7)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Mark D Powell]

Thank you, Erland

Using the Northwind Db for testing

select RIGHT('0000000'+CAST(CategoryID AS VARCHAR),7), CategoryId
from Categories

0000001 1
0000002 2
0000003 3
0000004 4
0000005 5
0000006 6
0000007 7
0000008 8

-- Mark D Powell --