How to update ClientCode ?

Deepak M

7y812

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.

4 Replies

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

Amit Mohanty7y ago
Accepted 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 <= @COUNT1
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 Jain7y ago
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 M7y ago
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 Jain7y ago
Do you have something fixed pattern you need to define in ClientCode.

If yes you can write stored procedure for updating data