How to autoincrement a varchar?

Question

Can I make a primary key like 'c0001, c0002' and for supplier 's0001, s0002' in one table?

Answer 1

its old post but search for such problem then i find the correct code

CREATE TABLE [dbo].[ProductTBL](
    [Product_Id] [char](6) NOT NULL)

--[Product_Id] primary key
declare @NewIDD varchar(6);
DECLARE @i int;
SET @i=(select ISNULL( Convert(int, max(REPLACE(Product_Id,'P',''))+1),1) from ProductTBL);
SET @NewIDD=(select ISNULL( max(Product_Id),'P00001') from ProductTBL)
SET @NewIDD=REPLACE(@newIDD, right(@newIDD,LEN(@i)),@i);

Answer 2

you may try below code:

SET @variable1 = SUBSTR((SELECT id FROM user WHERE id = (SELECT MAX(id) FROM user)), 5, 7)+1;
SET @variable2 = CONCAT("LHPL", @variable1);
INSERT INTO `user`(`id`, `name`) VALUES (@variable2,"Jeet");

• 1st line to get last inserted Id by removing four character than increase one value and set to a variable1
• 2nd line to make complete id with four character prefix and assign to variable2
• insert new value with generated new primary key = variable2
• you should have minimum one data in this table to work above SQL

Answer 3

We can add Default Constraint Function with table definition to achieve this.

First create table -

create table temp_so (prikey varchar(100) primary key, name varchar(100))
go

Second create new User Defined Function -

create function dbo.fn_AutoIncrementPriKey_so ()
returns varchar(100)
as
begin
    declare @prikey varchar(100)
    set @prikey = (select top (1) left(prikey,2) + cast(cast(stuff(prikey,1,2,'') as int)+1 as varchar(100)) from temp_so order by prikey desc)
    return isnull(@prikey, 'SB3000')
end
go

Third alter table definition to add default constraint -

alter table temp_so
add constraint df_temp_prikey
default dbo.[fn_AutoIncrementPriKey_so]() for prikey
go

Fourth insert new row into table without specifying value for primary column-

insert into temp_so (name) values ('Rohit')
go 4

Check out data in table now -

select * from temp_so

OUTPUT -

prikey  name
SB3000  Rohit
SB3001  Rohit
SB3002  Rohit
SB3003  Rohit

Answer 4

INSERT INTO Yourtable (yourvarcharID) 
  values('yourvarcharPrefix'+(
      SELECT CAST((SELECT CAST((
      SELECT Substring((
      SELECT MAX(yourvarcharID) FROM [Yourtable ]),3,6)) AS int)+1) 
        AS VARCHAR(20))))

Here varchar column is prefixed with 'RX' then followed by 001, So I selected substring after that prefix of it and incremented the that number alone.

Answer 5

Yes, Actually these are two different questions, 1. Can we use varchar column as an auto increment column with unique values like roll numbers in a class

ANS: Yes, You can get it right by using below piece of code without specifying the value of ID and P_ID,

CREATE TABLE dbo.TestDemo
  (ID INT IDENTITY(786,1) NOT NULL PRIMARY KEY CLUSTERED,
   P_ID AS 'LFQ' + RIGHT('00000' + CAST(ID AS VARCHAR(5)), 5) PERSISTED,
   Name varchar(50),
   PhoneNumber varchar(50)
  )

2. Two different increments in the same column,

ANS: No, you can't use this in one table.

Answer 6

Instead of doing 'c0001, c0002' for customers and 's0001, s0002' for suppliers in one table, proceed in the following way:

• Create one Auto-Increment field "id" of Data Type "int (10) unsigned".
• Create another field "type" of Data Type "enum ('c', 's')" (where c=Customer, s=Supplier).

As "@PerformanceDBA" pointed out, you can then make the Primary Key Index for two fields "id" & "type", so that your requirement gets fulfilled with the correct methodology.

Answer 7

No. If you really need this, you will have to generate ID manually.

Answer 8

1. The idea in database design, is to keep each data element separate. And each element has its own datatype, constraints and rules. That c0002 is not one field, but two. Same with XXXnnn or whatever. It is incorrect , and it will severely limit your ability to use the data, and use database features and facilities.

   Break it up into two discrete data items:
   column_1 CHAR(1)
column_2 INTEGER

   Then set AUTOINCREMENT on column_2

   And yes, your Primary Key can be (column_1, column_2), so you have not lost whatever meaning c0002 has for you.

2. Never place suppliers and customers (whatever "c" and "s" means) in the same table. If you do that, you will not have a database table, you will have a flat file. And various problems and limitations consequent to that.

   That means, Normalise the data. You will end up with:

   • one table for Person or Organisation containing the common data (Name, Address...)
   • one table for Customer containing customer-specific data (CreditLimit...)
   • one table for Supplier containing supplier-specific data (PaymentTerms...)
   • no ambiguous or optional columns, therefore no Nulls
   • no limitations on use or SQL functions
     .

   And when you need to add columns, you do it only where it is required, without affecting all the other sues of the flat file. The scope of effect is limited to the scope of change.

Answer 9

Assigning domain meaning to the primary key is a practice that goes way, way back to the time when Cobol programmers and dinosaurs walked the earth together. The practice survives to this day most often in legacy inventory systems. It is mainly a way of eliminating one or more columns of data and embedding the data from the eliminated column(s) in the PK value.

If you want to store customer and supplier in the same table, just do it, and use an autoincrementing integer PK and add a column called ContactType or something similar, which can contain the values 'S' and 'C' or whatever. You do not need a composite primary key.

You can always concatenate these columns (PK and ContactType) on reports, e.g. C12345, S20000, (casting the integer to string) if you want to eliminate the column in order to save space (i.e. on the printed or displayed page), and everyone in your organization understands the convention that the first character of the entity id stands for the ContactType code.

This approach will leverage autoincrementing capabilities that are built into the database engine, simplify your PK and related code in the data layer, and make your program and database more robust.

Answer 10

First let us state that you can't do directly. If you try

create table dbo.t1 (
id varchar(10) identity,
);

the error message tells you which data types are supported directly.

Msg 2749, Level 16, State 2, Line 1 Die 'id'-Identitätsspalte muss vom Datentyp 'int', 'bigint', 'smallint', 'tinyint' oder 'decimal' bzw. 'numeric' mit 0 Dezimalstellen sein und darf keine NULL-Werte zulassen.

BTW: I tried to find this information in BOL or on MSDN and failed.

Now knowing that you can't do it the direct way, it is a good choice to follow @marc_s proposal using computed columns.

Answer 11

My approach would be:

• create an ID INT IDENTITY column and use that as your primary key (it's unique, narrow, static - perfect)

• if you really need an ID with a letter or something, create a computed column based on that ID INT IDENTITY

Try something like this:

CREATE TABLE dbo.Demo(ID INT IDENTITY PRIMARY KEY,
                      IDwithChar AS 'C' + RIGHT('000000' + CAST(ID AS VARCHAR(10)), 6) PERSISTED
                     )

This table would contain ID values from 1, 2, 3, 4........ and the IDwithChar would be something like C000001, C000002, ....., C000042 and so forth.

With this, you have the best of both worlds:

• a proper, perfectly suited primary key (and clustering key) on your table, ideally suited to be referenced from other tables

• your character-based ID, properly defined, computed, always up to date.....

Answer 12

I prefer artificial primary keys. Your requirements can also be implemented as unique index on a computed column:

CREATE TABLE [dbo].[AutoInc](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Range] [varchar](50) NOT NULL,
  [Descriptor]  AS ([range]+CONVERT([varchar],[id],(0))) PERSISTED,
  CONSTRAINT [PK_AutoInc] PRIMARY KEY ([ID] ASC)
)

GO

CREATE UNIQUE INDEX [UK_AutoInc] ON [dbo].[AutoInc] 
(
    [Descriptor] ASC
)

GO