How to create auto generate clientID as string + number?

Question

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.

Sonu Gupta · 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 Gandhi · Answer

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 C · Answer

hi 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 Mohanty · Answer

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