How to create auto generate clientID as string + number?

Chandrasekaran C

7y1.5k

Hi,

I don't know mssql to create client ID like CLT001, CLT002.... like this as auto generate when insert query performing, In addition can I mark as private key.

I don't want the auto generate using identity value from numeric ID, I need combination for clientID auto generation.

Additional information.

I use Asp.net and Javascript, Ajax.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sonu Gupta7y ago
Accepted answer
Hi,

Try this -

CREATE TABLE #tbl_Client(
[ID] [int] IDENTITY(1,1) NOT NULL,
[ClientID] AS ('CL' + RIGHT('000' + CAST(Id AS VARCHAR(6)), 6)) PERSISTED,
[name] VARCHAR(50),
CONSTRAINT [PK_AutoInc] PRIMARY KEY ([ID] ASC)
)
INSERT INTO #tbl_Client ([name]) VALUES('Company1')
INSERT INTO #tbl_Client ([name]) VALUES('Company2')
INSERT INTO #tbl_Client ([name]) VALUES('Company3')
select *from #tbl_Client
Nirav Gandhi7y ago
Please use new CONCAT function which is available from SQL server 2014.
CREATE TABLE table1(Date DATE, Val INTEGER);

Insert into table1 select '2019-01-01',1

SELECT CONCAT('CLT', max(val)+1) from table1

Chandrasekaran C7y ago

I have table

[tbl_Client]

( 	[Id] [int] IDENTITY(1,1) NOT NULL,

	[ClientID] [nvarchar](50) NOT NULL,

	[name] [varchar](MAX) NULL )

I want Client ID CL0001, CL0002, ........CL9999.

if it reached A9999 it should new series like CM0001,CM0002,..........CM9999.

then CN,....ZZ

I am looking sql function or store procedure which will return max Series No.

asp.net Coding

Javascript & Jquery coding..

function ClientSave(){

var name1= document.getelmentbyID('txtName');

///After ajax calling web service to insert data..

// insert query like /// insert into tblClient(name) values ( '" + name1 +" ')

}

But I need the ClientId is create when during this insert query performing but I don't know how happen this...

I get this function in sql but how can I call this

CREATE FUNCTION getMaxNo() RETURNS VARCHAR(5) AS BEGIN DECLARE @Max_no varchar(5), @numeric int SELECT @Max_no= ISNULL(MAX(BillNo),'A0001') FROM [tbl_CashCounter] SET @numeric=CAST(RIGHT(@Max_no,4) AS INT)+1 SELECT @Max_no=
(CASE WHEN RIGHT(@Max_no,4)=9999 THEN char(ASCII(left(@Max_no,1))+1)+'0001' ELSE left(@Max_no,1)
 END) +''+ ISNULL(CASE WHEN len(@numeric)=1 THEN '000'+CAST(CAST(RIGHT(@Max_no,4) AS INT)+1 AS VARCHAR(4)) WHEN len(@numeric)=2 THEN '00'+CAST(CAST(RIGHT(@Max_no,4) AS INT)+1 AS VARCHAR(4)) WHEN len(@numeric)=3 THEN '0'+CAST(CAST(RIGHT(@Max_no,4) AS INT)+1 AS VARCHAR(4)) WHEN len(@numeric)=4 THEN ''+CAST(CAST(RIGHT(@Max_no,4) AS INT)+1 AS VARCHAR(4)) END,'') RETURN @Max_no END	 GO

Amit Mohanty7y ago
Hey try this
SELECT 'CLT' + (SELECT RIGHT('0000' + CAST(ISNULL(MAX(ID),0) + 1 AS NVARCHAR(4)),4) FROM tblYourTable)