How can I auto-increment an alphanumeric field with idendity

Question

How can I auto-increment an alphanumeric field with idendity

Navigation

#1 by (3 votes)
#2 by (0 votes)

1

CREATE TABLE RESPONSABLE(
COD_RES     CHAR(6) INDENTITY(¿?) PRIMARY KEY,
NOM_RES     VARCHAR(25) NOT NULL,
APEPAT_RES  VARCHAR(25) NOT NULL,
APEMAT_RES  VARCHAR(25) NOT NULL
)
GO

INSERT INTO RESPONSABLE VALUES('RES001', 'Pedro', 'Abanto', 'Mendoza')
SELECT * FROM RESPONSABLE 
GO

sql-server

asked by Luis Felipe Escala Torres 19.11.2016 в 23:44

source

2 answers

Store text variable in a char array SQL Error 18456 when connecting to the server

score 3 · Answer 1

It is true what has been said about the fact that you can not do an auto-incremental with text. But there is a trick that could help you.

Define a column INT IDENTITY in your table

Add a calculated column that is the combination between a string and the ID :

Something like this:

    CREATE TABLE dbo.TuTabla
       (ID INT IDENTITY(1,1),
        COD_RES AS 'RES_' + CAST(ID AS VARCHAR(10)) PERSISTED, 
        ......
       )

This way, your column ID is a IDENTITY normal, and COD_RES will have values like RES_1, RES_2, RES_3, ......, RES_50

With the statement persisted the column is saved on the disk, and it is not necessary to calculate it every time the table is consulted.

Note : If you use my suggestion, your insert would be like this:

INSERT INTO RESPONSABLE VALUES('Pedro', 'Abanto', 'Mendoza')

You would not specify the value of the Id (which is autoincremental) nor the calculated field, because it will be automatically generated from the text you have predefined along with the Identity value.

score 0 · Answer 2

0

You can not auto-increment an alphanumeric, as much as you can create a trigger that calculates the value based on a predetermined criterion.

answered by 20.11.2016 в 00:55