Need to update value in string

Question

Create Table #Temp
(
Code VarChar(60)
)
Insert Into #Temp(Code)
Values
('20288650,0,50030361,09 Feb 2022'),
('637468,0,50030361,22 Mar 2022'),
('6374690,0,50030361,22 Mar 2022')

I have to update 50030361 values with some value. How i can achieve this. I am using SQL Server2014.

I can create a function to split the data and can update. Instead of that is there any other way. Please guide me on this.

Answer 1

Hi，@Murari Ram

Welcome to Microsoft T-SQL Q&A Forum!

Please check this：

select code,STUFF(code,charindex( ',', code, (charindex( ',', code) + 1))+1,  
 charindex( ',', code, (charindex( ',', code, (charindex( ',', code) + 1)) + 1))-  
 charindex( ',', code, (charindex( ',', code) + 1))-1  
      
 ,'test_value')as Update_Coulumn from #temp  

Tip: The three different charindex( ',', code, (charindex( ',', code) + 1)) here is to calculate the position of the comma.

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi,

I have to update 50030361 values with some value.

It can help if you provide the expected result set, but if I understood you correctly then this is what you want can be done with simple REPLACE

-- SELECT with new value
SELECT Code, REPLACE(Code, '50030361','New_Value')
FROM #Temp

-- UPDATE values in the table
UPDATE #Temp
    SET Code = REPLACE(Code, '50030361','New_Value')
SELECT Code FROM #Temp
GO

Answer 3

Hi @Murari Ram

please check out this query statement

 ;WITH CTE AS(select *,FIRSTCOMMAS = CHARINDEX(',',Code), SECONDCOMMAS = CHARINDEX(',',Code,CHARINDEX(',',Code)+1) ,THIRDCOMMAS = CHARINDEX(',',Code,CHARINDEX(',',Code,CHARINDEX(',',Code)+1)+1) from #Temp )  
    SELECT * ,NEWCODE = STUFF(Code,SECONDCOMMAS+1,THIRDCOMMAS-SECONDCOMMAS-1,'1234') FROM CTE  
  --- update value in the table  
;WITH CTE AS(select *,FIRSTCOMMAS = CHARINDEX(',',Code), SECONDCOMMAS = CHARINDEX(',',Code,CHARINDEX(',',Code)+1) ,THIRDCOMMAS = CHARINDEX(',',Code,CHARINDEX(',',Code,CHARINDEX(',',Code)+1)+1) from #Temp )  
update #Temp set Code =(SELECT NEWCODE = STUFF(Code,SECONDCOMMAS+1,THIRDCOMMAS-SECONDCOMMAS-1,'1234') FROM CTE WHERE CTE.Code = #Temp.Code)  
 
select * from #Temp  

Best Regards,
Isabella

---

If the answer is the right solution, please click "Accept Answer" and upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

If your code always consists of 4 values, then you can also use PARSENAME function, e.g.

Create Table #Temp
(
Code VarChar(60)
)
Insert Into #Temp(Code)
Values
('20288650,0,50030361,09 Feb 2022'),
('637468,0,50030361,22 Mar 2022'),
('6374690,0,50030361,22 Mar 2022')

DECLARE @newValue VARCHAR(15) = 'Testing';

SELECT Code, PARSENAME(X.Code2, 2) AS [ValueToReplace],
CONCAT(PARSENAME(x.Code2,4),',', PARSENAME(x.Code2,3), ',',
@newValue, ',', PARSENAME(x.Code2,1)) AS ReplacedValue


FROM #Temp
CROSS APPLY (SELECT REPLACE(Code, ',','.') AS Code2) X