0

How to auto-generate a variable of data type VARCHAR without a certain limit which means It has to be like 'AB1', 'AB2', 'AB3' ... up to infinity

Here there are mainly two points:

  1. There is no identity column in this table

  2. And sometimes it may have another pattern data in this column like 'AB1','AB2','AB3','PK1','AB4' Which is (AB(3+1))

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
sai bharath
  • 792
  • 2
  • 10
  • 22
  • Duplicate http://stackoverflow.com/questions/17893988/how-to-make-mysql-table-primary-key-auto-increment-with-some-prefix – JoxieMedina Jan 05 '17 at 04:43
  • You want this to happen in a create table statement or you want it in a select query? – PowerStar Jan 05 '17 at 06:43
  • The pattern will be similar or can we expect records like 'ABC123'? – Gopakumar N.Kurup Jan 05 '17 at 06:43
  • So infinity would be a problem. A `varchar(max)` field in SQL Server can hold 2^31-3 characters - hardly enough to support infinity! See: http://stackoverflow.com/questions/1761124/how-many-characters-in-varcharmax – STLDev Jan 06 '17 at 02:11

1 Answers1

1

Wish it can helpful for you.

CREATE TABLE #tt(s VARCHAR(100),col1 INT )
INSERT INTO #tt
VALUES('AB1',1),('AB2',100),('AB3',2),('PK1',5),('AB4',20)
GO

DECLARE @prefix VARCHAR(100)='PK'
INSERT INTO #tt(s,col1)
SELECT @prefix+LTRIM(ISNULL(MAX(CASE WHEN ISNUMERIC(v.num)=1 THEN CONVERT(INT,v.num) ELSE 0 END),0)+1),20
FROM #tt 
CROSS APPLY(VALUES(SUBSTRING(s,LEN(@prefix)+1,LEN(s)))) v(num)
WHERE LEFT(s,LEN(@prefix))=@prefix
GO 10

SELECT * FROM #tt

s
-----
AB1
AB2
AB3
PK1
AB4
PK2
PK3
PK4
PK5
PK6
PK7
PK8
PK9
PK10
PK11
PK12
PK13
PK14
PK15
PK16
PK17
PK18
PK19
PK20
PK21
Nolan Shang
  • 2,312
  • 1
  • 14
  • 10