Find a string inside a string and update in SQL Server

Question

I have a table like this :

create table Fbank (id int, COCODE nvarchar(max))

insert into Fbank 
values (1, '<row xml:space="preserve" id="174580000041250.040002">
  <c1>HTG115960001</c1>
  <c2>14013514,48</c2>
  <c3>222</c3>
  <c4>BAL MIGRATED</c4>
  <c5>NULL</c5>
  <c6>NULL</c6>
  <c7>NULL</c7>
  <c8>9900</c8>
  <c9>11596</c9>
  <c10>20151017</c10>
  <c11>HTG</c11>
  <c12>NULL</c12>
  <c13>NULL</c13>
  <c14>FT1529010083</c14>
  <c15>1</c15>
  <c16>FT1529010083</c16>
  <c17>FT</c17>
  <c18>20151017</c18>
  <c19>NULL</c19>
  <c20>0</c20>
  <c21>5033_INPUTTER__OFS_AUTO.FT</c21>
  <c22>5033_INPUTTER_OFS_AUTO.FT</c22>
  <c23>1510181127</c23>
  <c24>HG0010001</c24>
</row>')

I need to replace the string inside the tag c24 tag..

Excepted output: I need to replace the letter 'G' to be replaced with 'T' like this inside c24 tag - HT0010001 and all the rest should be same unchanged...

What I tired:

declare @searchText varchar(20) = (
select SUBSTRING(COCODE,CHARINDEX('<c24>', COCODE),  7)
from FBNK_EB_SFF_ACCT_HI000)

update Fbank 
set cocode = STUFF(@searchText, CHARINDEX('<c24>', COCODE), 1, 'T')

If you stored your XML in the xml datatype, this would be such easier. — Thom A
– Thom A ♦, Commented Jul 6, 2020 at 16:13
Your question clearly shows it is not, @Mar1009 : COCODE nvarchar(max). The xml and nvarchar data types are not the same. — Thom A
– Thom A ♦, Commented Jul 6, 2020 at 16:15
@Mar1009 Is the datatype in your real table XML, or is it NVARCHAR(MAX) like your example shows? There's a big difference. — squillman
– squillman, Commented Jul 6, 2020 at 16:15

pwilcox · Accepted Answer · 2020-07-06 16:30:02Z

3

If the code always begins with 'H', then I imagine it's easier to just have '<c24>H' be part of the string to replace, but then add it back again:

update Fbank 
set    COCODE = replace(cocode, '<c24>HG', '<c24>HT');

If it doesn't, then I'd say you're on the right track with charindex and substring. But use patindex instead, and update fbank directly, don't put the search term into any variable. If you're trying to split up your logic into parts, consider cross apply:

update      fb 
set         cocode = stuff(cocode, ix, 1, 'T')
from        Fbank fb
cross apply (select ix = patindex('%<c24>%', cocode) + 6) ap
where       substring(cocode, ix, 1) = 'G'

edited Jul 6, 2020 at 16:30

answered Jul 6, 2020 at 16:14

pwilcox

5,8041 gold badge23 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Find a string inside a string and update in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related