How do i create a Identity starting with a Letter first then numbers after?

Question

I want to create data that contains info about my supplier making it auto generate his ID. for e.g: SupplierID i want it to appear as - SID001, SID002 all to auto genrate after each other.

How do i do this with SQL?

Please let me know thanks!

Answer 1

You could check for the next ID available and concatenate your prefix with that value. The result should be inserted in the SupplierID column.

Answer 2

Some databases(Oracle, postgres etc) support a sequence for number part. Some(mysql) have an auto increment feature, so you get new number when inserting.

You could then concatenate with string to generate string based IDs.

Answer 3

You could just use a sequence or identity column and store the prefix in a separate column, or alternately, if the table in question will only ever have suppliers with a prefix of SID, don't store the SID part at all and simply add it at the application level.

Answer 4

Try this: http://forums.asp.net/p/1441819/3267338.aspx

Answer 5

Ask yourself this: what are the costs of doing this? In particular, what is required to compare two strings versus comparing two numbers? To generate strings from numbers?

Then ask yourself, what value is added by having an id of 'SID0001' rather than just 001?

Then ask yourself, is there an easy way to display a prefix without redundantly storing it for each row? (Answer: yes, with a database view).