Another Newbie Auto Question

[ironvid]

I am using MS SQL 2000

I have A column which contains a unique auto number
1
2
3
4 and so on

How can I format it so they read:
CDI-1
CDI-2
CDI-3
CDI-4 and so on

The data type for the column is set to 'int' and at present it says the length of the field is 4 but I need it to go into the millions e.g.

CDI-5991991

I am a Complete begineer to sql so the more detail the better.

Thanks

Regards...stephen

 

[hmckillop]

Do you want to format it when reading it, or actually in the table?
I will assume you it is a read only as it doesnt make much sense to add CDI- in front of your primary key.

INT in sql server will allow 2^31 (2,147,483,647)
before it will expire. If you need something bigger try a bigint, which is 2^63 (9,223,372,036,854,775,807)

The field size of 4 is the number of bytes used in storing the data.

As far as outputting your data if you wanted to do a direct output as CDI-1 etc
try
SELECT 'CDI-' + Cast(ID as VARCHAR(12)) FROM MyTable
Where ID is the ID field in your table, and MyTable is the tablename.

Is this what you require?

 

[JamesLean]

You can't get SQL Server to auto-generate values like those you want. If the initial letters are always going to be the same (CDI-) then I suggest you keep the setup as it is and just add the letters when you're displaying the ID value in the app.

Also, the length of an int column is always 4. In this case it is referring to the storage size in bytes rather than the number of digits. Integer columns can hold values from -2,147,483,648 to 2,147,483,647.

--James