How to update ClientCode ?

Question

Hii to all, I have a CilentsData table with column name RowId, CilentCode, ClientName, RegDate having more than 10 thousand records. Now my problem is that I have to change the format of clientcode. For example, if the ClientCode is NR121 then I have to change it to CR/121/2013.

Amit Mohanty · Answer

Hey try this. DECLARE @TB TABLE ( SLNO INT ,ID INT ,CODE NVARCHAR(10),REGDATE DATETIME ) INSERT INTO @TB SELECT ROW_NUMBER() OVER( ORDER BY ROWID) AS ROWNUMBER, ROWID, SUBSTRING (CLIENTCODE, PATINDEX( '%[0-9]%' , CLIENTCODE), LEN(CLIENTCODE)),REGDATE FROM CLIENTSDATA ORDER BY ROWID DECLARE @ COUNT INT = 1 DECLARE @COUNT1 INT = ( SELECT COUNT (*) FROM @TB) WHILE @ COUNT BEGIN DECLARE @CODE NVARCHAR(10) = ( SELECT CODE FROM @TB WHERE SLNO = @ COUNT ) DECLARE @ YEAR NVARCHAR(4) = ( SELECT DATEPART( YEAR ,REGDATE) FROM @TB WHERE SLNO = @ COUNT ) DECLARE @ID INT = ( SELECT ID FROM @TB WHERE SLNO = @ COUNT ) UPDATE CLIENTSDATA SET CLIENTCODE = 'CR/' + @CODE + '/' + @ YEAR WHERE ROWID=@ID SET @ COUNT = @ COUNT + 1 END You can use the above code as stored procedure also.

Priyanka Jain · Answer

Check below code it may help to find your solution, CREATE FUNCTION dbo.udf_GetNumeric (@strAlphaNumeric VARCHAR(256)) RETURNS VARCHAR(256) AS BEGIN DECLARE @intAlpha INT SET @intAlpha = PATINDEX( '%[^0-9]%' , @strAlphaNumeric) BEGIN WHILE @intAlpha > 0 BEGIN SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' ) SET @intAlpha = PATINDEX( '%[^0-9]%' , @strAlphaNumeric ) END END RETURN ISNULL(@strAlphaNumeric,0) END GO create table #Client ( ID int , ClientCode VARCHAR(50), ClientName varchar(50), RegDate Datetime ) insert into #Client VALUES (1, 'NR121' , 'Nichole' , '2013-11-04 00:00:00' ) insert into #Client VALUES (2, 'HM122' , 'Nichole' , '2014-03-28 00:00:00' ) insert into #Client VALUES (3, 'TC124' , 'Nichole' , '2014-04-06 00:00:00' ) insert into #Client VALUES (4, 'TP125' , 'Nichole' , '2014-05-10 00:00:00' ) insert into #Client VALUES (5, 'KE126' , 'Nichole' , '2014-05-03 00:00:00' ) insert into #Client VALUES (6, 'EB127' , 'Nichole' , '2014-06-17 00:00:00' ) insert into #Client VALUES (7, 'KCC129' , 'Nichole' , '2014-07-01 00:00:00' ) insert into #Client VALUES (8, 'CJ130' , 'Nichole' , '2014-07-04 00:00:00' ) create table #temp ( ID int , CodeVal VARCHAR(2), CodeNum varchar(10), RegDate varchar(4) ) insert into #temp select ID, 'CR' ,dbo.udf_GetNumeric(ClientCode),Year(RegDate) from #Client select * from #temp Update c SEt c.ClientCode = CONCAT(t.CodeVal, '/' ,t.CodeNum, '/' ,t.RegDate) FROM #Client c,#temp t Where c.Id = t.ID select * from #Client The output it will display,

Deepak M · Answer

Yes, Priyanka Jain there is a fixed pattern CR\XXX\XXXX CR -- Fixed value XXX -- Numeric part of existing clientcode XXXX -- Year of regddate.

Priyanka Jain · Answer

Do you have something fixed pattern you need to define in ClientCode. If yes you can write stored procedure for updating data

How to update ClientCode ?

Answers (4)