Hi,

I am new to SQL. I have 2 fields in tableOrder, OrderID and OrderNum.
OrderID is
Identity:yes. Is there a way that I can set OrderNum to be "J"+OrderID and
always has 8 characters automatically in SQL2000?

OrderID OrderNum
---------- --------------
1 J0000001
2 J0000002
10 J0000010
11 J0000011
123 J0000123

I really appreciate your help and thanks in advance.

Jeff

Report this post to a moderator

You can have a computed column as shown in this example:

CREATE TABLE dbo.Orders
(
OrderID int IDENTITY(1, 1),
OrderNUM AS 'J' + RIGHT('0000000' + LTRIM(STR(OrderID)), 7)
)

INSERT INTO dbo.Orders DEFAULT VALUES
INSERT INTO dbo.Orders DEFAULT VALUES
INSERT INTO dbo.Orders DEFAULT VALUES
INSERT INTO dbo.Orders DEFAULT VALUES
INSERT INTO dbo.Orders DEFAULT VALUES
INSERT INTO dbo.Orders DEFAULT VALUES
INSERT INTO dbo.Orders DEFAULT VALUES
INSERT INTO dbo.Orders DEFAULT VALUES
INSERT INTO dbo.Orders DEFAULT VALUES
INSERT INTO dbo.Orders DEFAULT VALUES

SELECT * FROM dbo.Orders

--
HTH,
Vyas, MVP (SQL Server)
SQL Server FAQ, articles, code samples, interview questions and more @
http://vyaskn.tripod.com/




"Jeff" <lpr815@hotmail.com> wrote in message
news:uSUJd3hHCHA.1740@tkmsftngp10...
Hi,

I am new to SQL. I have 2 fields in tableOrder, OrderID and OrderNum.
OrderID is
Identity:yes. Is there a way that I can set OrderNum to be "J"+OrderID and
always has 8 characters automatically in SQL2000?

OrderID OrderNum
---------- --------------
1 J0000001
2 J0000002
10 J0000010
11 J0000011
123 J0000123

I really appreciate your help and thanks in advance.

Jeff

Report this post to a moderator